CODEWITHSUNDEEP
mysql
systemdebt
systemdebt

Reputation: 4941

using count and joins across multiple tables

Example: Table 1:

id   name

1    sim
2    sam
3    jas

Table 2

key   age
1     10
1     20
2     40
3     10

Table 3:

id    rating
2       7
2       6
3       8
3       7
1       9

Now, what I need is

number of rows that were grouped using group by in both the tables i.e table A and table B.

i.e 

select t1.id, count(t2.key) as a, count(t3.id) as b
FROM 
table1 t1
LEFT JOIN 
table2 t2
ON 
t1.id = t2.key
LEFT JOIN 
table3 t3
ON 
t1.id = t3.id
GROUP BY t1.key, t2.id

Result I expect:

id   a  b
1    2  1
2    1  2
3    1  2

Upvotes: 0

Views: 23

Answers (1)

Barmar
Barmar

Reputation: 780724

You're getting duplicates because you're creating a cross product between all 3 tables. Use COUNT(DISTINCT) to filter out the duplicates.

select t1.id, count(DISTINCT t2.age) as a, count(DISTINCT t3.rating) as b
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.key
LEFT JOIN table3 t3 ON t1.id = t3.id
GROUP BY t1.id

Upvotes: 1

Related Questions