CODEWITHSUNDEEP
mysql
Ali R.
Ali R.

Reputation: 28

MySQL Left Join - how to get count of records found in 2nd table

Table1

id    Name

1     John

2     Sheldon

3     Sarah

========

Table2

rid   id  id2  relation

1     1    2    1

2     1    3    1

How can i get all members from table 1 in one query and each member's total relations.

The result I want should be:

Jhon       2

Sheldon    0

Sarah      0

Upvotes: 0

Views: 88

Answers (2)

Pank
Pank

Reputation: 14268

select Table1.name, count(Table2.id) from Table1 LEFT Join Table2 on Table1.id=Table2.id group by Table2.id

    OR 

select Table1.name,IFNULL(count(Table2.id), 0) from Table1  LEFT Join Table2  on Table1.id=Table2.id group by Table2.id

enter image description here

Upvotes: 3

sashkello
sashkello

Reputation: 17871

SELECT t1.name, IFNULL(COUNT(t2.id2), 0)
FROM table1 t1 LEFT JOIN table2 t2 ON t1.id=t2.id
GROUP BY t1.id

Upvotes: 1

Related Questions