Jump to content

multiple joins problems


mindloop

Recommended Posts

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_date
FROM a
LEFT JOIN b ON a.a_id = b.a_id
LEFT JOIN c ON a.a_id = c.a_id
GROUP 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.
Link to comment
https://forums.phpfreaks.com/topic/36465-multiple-joins-problems/
Share on other sites

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?
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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.