CODEWITHSUNDEEP
mysqlsqljoincount
Mirgorod
Mirgorod

Reputation: 32653

Mysql multiple COUNT and join

I have table "tree".

I have query: SELECT * FROMtreeWHEREpid=10 This query returns 10 items.

I want to get something like that in result:

id | pid | title | subElements
11 | 10  | t 1   | 12
12 | 10  | t 2   | 16
13 | 10  | t 3   | 0
...

How too build join query to count sub items for this 10 items?

Upvotes: 0

Views: 216

Answers (1)

amaters
amaters

Reputation: 2316

try this:

SELECT t1.id, t1.pid, t1.title , count(t2) as subElements FROM tree as t1 
LEFT JOIN tree as t2 ON t2.pid = t1.id
WHERE t1.pid=10
GROUP BY t1.id, t1.pid, t1.title

Upvotes: 1

Related Questions