MySQL Forums
Forum List  »  Newbie

Multiple left joins and counts
Posted by: Does it matter
Date: May 26, 2015 04:05AM

Hello.

I am trying to work out a query.
I have 3 tables, one main table (t1) and 2 other tables with rows that may refer to the rows in t1.
What I want is to have the rows in t1 with count of rows in t2 and t3 matching each element in t1.

So far, my query is :

SELECT t1.* , COUNT(t2.id), COUNT(t3.id)
FROM table1 AS t1
LEFT JOIN
table2 AS t2
ON t1.id_t1 = t2.id_t1
LEFT JOIN
table3 AS t3
ON t1.id_t1 = t3.id_t1
GROUP BY id_t1

id_t1 is the primary key.

But it does not return what I want.

Example : let's say I have one row in t1 with id_t1=5, 2 rows in t2 with id_t1=5 and 3 rows in t3, instead of returning 2 and 3 as values for the count, it returns 6 and 6.

How do I work this out ?

Thanks.

Options: ReplyQuote


Subject
Written By
Posted
Multiple left joins and counts
May 26, 2015 04:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.