Mysql multiple COUNT and join

Question

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?

Ok, but how much queries I need to do to get count of sub elements for this 10 items? — Mirgorod
– Mirgorod, Commented May 10, 2012 at 11:08

amaters · Accepted Answer · 2012-05-10 11:23:17Z

1

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

edited May 10, 2012 at 11:23

answered May 10, 2012 at 11:09

amaters

2,3362 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

zerkms Over a year ago

It is a tree structure with unknown depth

amaters Over a year ago

I tried it on one of my tables (changed INNER JOIN to LEFT JOIN) and it worked. My table also has unknown depth of pid

zerkms Over a year ago

and how this query is supposed to perform recursive traverse down the tree? PS: I don't even mention that WHERE is on a wrong place and that COUNT(t2) would cause error

amaters Over a year ago

sorry about the WHERE... my bad. About the recursive part: sorry... didnt get that part of the question right because i thought it had to do with direct children. Looked like @Mirgorod only needed those.

Collectives™ on Stack Overflow

Mysql multiple COUNT and join

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related