Page 1 of 1
bit of a hairy query
Posted: Tue Jan 04, 2005 12:52 pm
by magicrobotmonkey
I have three tables and I am trying to link them in a certain way:
I want to return every row from table A
I want to return the SUM of a column in each row of TAble B associated with table A
Likewise for table C
But, table A may or may not have rows from B or C Assoicated with it, so I can 't use an inner join and if I use a left join, it only returns one row's data even if it has multiple B or C rows. I think I'm missing something obvious.
Posted: Tue Jan 04, 2005 1:42 pm
by feyd
something like this
Code: Select all
SELECT table_a.*, SUM( table_b.col ) b_col, SUM( table_c.col ) c_col FROM table_a
LEFT JOIN table_b ON table_a.link_b = table_b.id
LEFT JOIN table_c ON table_a.link_c = table_c.id
WHERE table_c.col IS NOT NULL AND table_b.col IS NOT NULL
Posted: Tue Jan 04, 2005 2:40 pm
by Weirdan
feyd wrote:something like this...
You've missed GROUP BY clause...
Posted: Tue Jan 04, 2005 2:43 pm
by feyd
this is what happens when I'm away from the computer for 6-8 weeks...

Posted: Tue Jan 04, 2005 3:06 pm
by magicrobotmonkey
right, but what to group by? table_a.id? - will the above query return a row for each mathing row on one of the left joins?
also, I dont think I want that where clause because I always want every row from table a to appear regardless of if it has any table bs or cs
Posted: Wed Jan 05, 2005 7:31 am
by Weirdan
magicrobotmonkey wrote:right, but what to group by? table_a.id? - will the above query return a row for each mathing row on one of the left joins?
After re-reading it once again I don't think so... some modifications are still required to make it work. To my understanding, it would be more logical to use one-to-many relation instead of one-to-one used by feyd. Something like this:
Code: Select all
select
table_a.*,
sum(table_b.field),
sum(table_c.field)
from
table_a.*
left join
table_b
on
table_b.a_id = table_a.id
left join
table_c
on
table_c.a_id = table_a.id
group by
table_a.id
Notice the ON clauses I've changed.
also, I dont think I want that where clause because I always want every row from table a to appear regardless of if it has any table bs or cs
for those rows which don't have any matching ones in table_c or table_b SUM() will return NULL. You might want to use [mysql_man]IFNULL[/mysql_man] function to convert it to 0: IFNULL(SUM(table_c.field), 0)
Posted: Wed Jan 05, 2005 8:55 am
by magicrobotmonkey
yea thats what I finally came up with. And I prefer the nulls in this case, but thanks for the heads up on the IFNULL. I think I just had some kind of mind block yesterday. The actually query was a little bit trickier because the b and c tables are actually just the same table aliased and each have a one to one relationship to another table from where I was actually getting the info. so mostly just simplifying it to post it here helped.