CODEWITHSUNDEEP
mysqlsql
chandan
chandan

Reputation: 104

how to link two tables

I have one sql table with two columns in which one will act has a parent and another will act has a child.

please find the below table 

Id  name    parent
1   Chandan null
2   raju    1
3   anand   1
4   gsfsgs  null
8   gggg    2
9   ffff    2

Here I need to get the below out has

 Id  name     count
 1   Chandan    2
 2   raju       2
 3   anand      0
 4   gsfsgs     0
 8   gggg       0
 9   ffff       0

Here what I'm trying

select id, name, (select count(*) from table where parentid=id) as count

But I'm not getting the proper result.

Upvotes: 1

Views: 72

Answers (3)

kjmerf
kjmerf

Reputation: 4345

Here is a variation using a subquery AND a join:

SELECT T.id, T.name, IFNULL(sub.cnt,0)
FROM T
LEFT JOIN
(SELECT parent, COUNT(*) AS cnt
FROM T
GROUP BY parent) sub
ON T.id = sub.parent

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

Do a self-join

SELECT t1.Id,
       t1.name,
       COUNT(t2.Id) AS count
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t1.Id = t2.parent
GROUP BY t1.Id,
         t1.name

Upvotes: 5

jarlh
jarlh

Reputation: 44766

The sub-query needs to be correlated, i.e. it needs a reference to the outer query:

select id, name, (select count(*) from table t2 where t2.parentid = t1.id) as count
from table t1

However, a left join will probably have better performance.

Upvotes: 2

Related Questions