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
