List:General Discussion« Previous MessageNext Message »
From:Micah Stevens Date:January 27 2009 11:25pm
Subject:Help with a query.
View as plain text  
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!


Thread
Help with a query.Micah Stevens28 Jan
  • Re: Help with a query.Peter Brawley28 Jan
    • Re: Help with a query.Micah Stevens28 Jan