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!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
