From: Peter Brawley
Date: January 27 2009 11:42pm
Subject: Re: Help with a query.
List-Archive: http://lists.mysql.com/mysql/216052
Message-Id: <497F9BE5.4040009@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------090007030503080406070107"

--------------090007030503080406070107
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Micah,

 >each item in `a` has a 1 to 1 relationship to `b`,
 >and each item in `c` has a 1 to 1 relationship with `b`.
 >Sometimes these correspond, i.e. there's a row in `b`
 >that relates to both `a` and `c`, but not always.

So in a given b row, the b_id value might match an a.a_id, a c.a_id, or 
both? Whatever the purpose of this ambiguity, it seems to undermine your 
query objective.

PB

-----

Micah Stevens wrote:
> Hi,
>
> I'm somewhat stumped by how to set up a single query that does the 
> following. Currently I'm accomplishing this through multiple queries 
> and some PHP 'glue' logic, but it should be possible in a single query 
> I think, and it's bugging me that I can't figure it out. If anyone has 
> any ideas, I'd appreciate it. Here's the situation:
>
> CREATE TABLE `a` (
> a_id int(11) AUTO_INCREMENT,
> b_id int(11),
> a_date datetime
> );
>
> CREATE TABLE `b` (
> b_id int(11) AUTO_INCREMENT,
> b_data varchar(128)
> );
>
> CREATE TABLE `c` (
> a_id int(11) AUTO_INCREMENT,
> b_id int(11),
> c_date datetime
> );
>
> each item in `a` has a 1 to 1 relationship to `b`, and each item in 
> `c` has a 1 to 1 relationship with `b`. Sometimes these correspond, 
> i.e. there's a row in `b` that relates to both `a` and `c`, but not 
> always.
>
> What I'm doing is looking for a sum of data from a and c for a 
> particular date range that shows it's corresponding b.b_data row. Each 
> row should contain:
>
> count(a_id), b_id, b_data, count(c_id)
>
> and if there's no corresponding data, the columns should be null. Like 
> a three way left join to table b sort of, include all rows of b, and 
> if there's no corresponding data for a or c, just fill the columns 
> with nulls.
>
> I can get queries to execute like this:
>
> select count(a_id), b_id, b_data, count(c_id)
> from a
> right join b using (b_id)
> left join c using (b_id)
> group by b_id
>
> but it's different results than when I do two inner joins (one for a&b 
> and one for b&c) and combine the data.
>
> Any thoughts?
>
> Thank you!
>
>
>
> ------------------------------------------------------------------------
>
>
> Internal Virus Database is out of date.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.176 / Virus Database: 270.10.12/1909 - Release Date: 1/22/2009 7:08 AM
>
>   

--------------090007030503080406070107--