Bring "children" record count along to all table records

Asked

Viewed 943 times

2

How do I count how many "children" such a row have, and at the same time list all the records in the table?
I have the table categorias, with the following data: 

 _________________________________
| id (PK)  |  nome  | id_pai (FK) |
 ---------------------------------
|     1    |  ctg1  |     NULL    |
|     2    |  ctg2  |      1      |
|     3    |  ctg3  |      1      |
 ---------------------------------

If I do SELECT *, COUNT(id_pai) FROM categorias GROUP BY id I have the following result:

 ___________________________________________________
| id (PK)  |  nome  | id_pai (FK) |  COUNT(id_pai)  |
 ---------------------------------------------------
|     1    |  ctg1  |     NULL    |        0        |
|     2    |  ctg2  |      1      |        1        |
|     3    |  ctg3  |      1      |        1        |
 ---------------------------------------------------

The result I hope is:

 ___________________________________________________
| id (PK)  |  nome  | id_pai (FK) |  COUNT(id_pai)  |
 ---------------------------------------------------
|     1    |  ctg1  |     NULL    |        2        |
|     2    |  ctg2  |      1      |        0        |
|     3    |  ctg3  |      1      |        0        |
 ---------------------------------------------------

I know the query is fundamentally wrong, because you’re telling me the part I don’t want. But how do I bring the amount of children that the current line has to the complete information?

mysql sql query

  • I don’t know if it would work, I can’t test it, but it would be something like... SELECT *, (SELECT Count(*) FROM categorias WHERE id_pai = cat.id_pai) FROM categorias as cat ?

  • @Rafaelwithoeft Almost that. It returned as in the query I have, only instead of Count working 1, it worked 2. I wanted ctg1 Count to work 2, and the other 0.

  • 2

    Try to change to SELECT *, (SELECT Count(*) FROM categorias WHERE id_pai = cat.id) FROM categorias as cat

  • @Rafaelwithoeft This query returned what I expected. I found the solution in the meantime, but you can post your answer which I accept as a solution.

  • 1

    Thomas I believe it is best you put as answer the solution you use... :)

1 answer

1

I managed to reach the result by doing a LEFT JOIN in the table itself, joining with the query that had:

SELECT cat.*, COUNT(cat2.id) FROM categorias AS cat LEFT JOIN categorias cat2 ON cat2.id_pai = cat.id GROUP BY cat.id

Note: The @Rafaelwithoeft response in the comments also works. I’m putting this as a response to requests.

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.