Bring "child" records count next to all table records

Navigation
2

How do I count how many "children" that row has, and at the same time list all records in the table?
I have the categorias table, 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 get 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 that the query is fundamentally wrong, because it is counting the part that I do not want. But how do I bring the amount of children that the current line has next to the complete information?

    
asked by anonymous 11.03.2015 / 15:52

1 answer

1

I was able to get 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: @RafaelWithoeft's reply in the comments also works. I'm putting this in response to requests.

    
11.03.2015 / 17:10
Phone and CPF masks in TextField Quit get request as soon as you start another