mindloop Posted January 31, 2007 Share Posted January 31, 2007 Hello, I will paste my table structure here, then I will explain the problem I am encountering[tt]-------------------| table a |-------------------|a_id |description|-------------------| 1 | entry1 |-------------------| 2 | entry2 |-------------------[/tt][tt]---------------------------------------| table b |---------------------------------------|b_id|a_id|b_value| b_date |---------------------------------------| 1 | 1 | 200 |2007-01-30 11:33:40|---------------------------------------| 2 | 1 | 100 |2007-01-27 11:31:50|---------------------------------------[/tt][tt]---------------------------------------| table c |---------------------------------------|c_id|a_id|c_value| c_date |---------------------------------------| 1 | 1 | 400 |2007-01-31 11:33:47|---------------------------------------| 2 | 1 | 50 |2007-01-30 11:33:50|---------------------------------------| 3 | 2 | 50 |2007-01-20 11:30:50|---------------------------------------| 4 | 2 | 50 |2007-01-10 11:31:20|---------------------------------------[/tt]Now i run this query on the tables above:[code]SELECT a.a_id, sum( b_value ) AS b_total, sum( c_value ) AS c_total, count( b.b_id ) AS b_count, count( c.c_id ) c_count, max( b.b_date ) AS last_b_date, max( c.c_date ) AS last_c_dateFROM aLEFT JOIN b ON a.a_id = b.a_idLEFT JOIN c ON a.a_id = c.a_idGROUP BY a.a_id[/code]and I get the following result (which is obviously wrong)[tt]----------------------------------------------------------------------------------------------|a_id |description|b_total |c_total |b_count|c_count|last_b_date |last_c_date |----------------------------------------------------------------------------------------------|1 | entry1 |600 |900 | 4 |4 |2007-01-30 11:33:40|2007-01-31 11:33:47 |----------------------------------------------------------------------------------------------|2 | entry2 |NULL |100 | 0 |2 |NULL |2007-01-20 11:30:50 |----------------------------------------------------------------------------------------------[/tt]What is wrong? Well, i get incorrect values for b_count and c_count, aswell as for b_total, c_total when i have values for an a_aid in both ( b and c) tables.The first row of the result should look like this:[tt]----------------------------------------------------------------------------------------------|a_id |description|b_total |c_total |b_count|c_count|last_b_date |last_c_date |----------------------------------------------------------------------------------------------|1 | entry1 |300 |450 | 2 |2 |2007-01-30 11:33:40|2007-01-31 11:33:47 |----------------------------------------------------------------------------------------------[/tt]Yet i get clean results when there is data related to an entry in table a only in one of the two joined tables. Can anyone suggest a sollution for this query? i have tried so many possible sollutions and i am getting nowhere.thx in advance.I.Radu. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 31, 2007 Share Posted January 31, 2007 It's not wrong, it's what you asked for... if you have a one-to-many relationship, you'll get multiple records being summed, which is why if you have 2, you'll get "double" the sums you expect. Quote Link to comment Share on other sites More sharing options...
mindloop Posted January 31, 2007 Author Share Posted January 31, 2007 all right, i didn't mean this was a bug, it was a "wrong" result for me (meaning it was not the desired result). i need to get the number of entries from table c and the number of entries from table b. i need to get all this info in a single query (with possible subqueries of course) because i have some complex filters set on the resulting rows, therefor i cannot split it into 2 queries.any suggestion? Quote Link to comment Share on other sites More sharing options...
fenway Posted January 31, 2007 Share Posted January 31, 2007 You'll have to use subqueries, then... that group by that you tossed in at the end "masks" the fact that you're getting back more rows than you "think" you should (one for each unique combination of a,b and c). Just put the counts into scalar subqueries. Quote Link to comment Share on other sites More sharing options...
mindloop Posted January 31, 2007 Author Share Posted January 31, 2007 i heard the word subqueries before, i don't know how to do it though. could you post an example related to mine using subqueries? Quote Link to comment Share on other sites More sharing options...
fenway Posted January 31, 2007 Share Posted January 31, 2007 Just write the query that you'd normally use to get the counts, and then wrap that it parens, and alias the expression. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.