Newbi Newbi - 3 months ago 9
MySQL Question

SQL Select data with condition from result data

Table Structure :

|Category_id |Parent_id|
| 193 | 185 |
| 200 | 193 |
| 11 | 193 |
| 150 | 193 |
| 145 | 185 |
| 165 | 145 |
| 123 | 11 |


First query =
select * from table where parent_id = 185
, result :

|Category_id |Parent_id|
| 193 | 185 |
| 145 | 185 |


Then repeated with same table, second query =
select * from table where parent_id = 193
result:

|Category_id |Parent_id|
| 200 | 193 |
| 11 | 193 |
| 150 | 193 |


What I want to do is count (category_id from second query) with single query like

select *,(count(select * from table where parent_id = ..... )) AS count from table where parent_id = 185 order by count ASC
with result like this :

|Category_id |Parent_id| Count |
| 193 | 185 | 3 |
| 145 | 185 | 1 |


I know I can do it, if do it 1 by 1 but it's waste loading so much.
it's possible build like that?

Thanks Advance.

Answer
SELECT 
TT1.*,
  (
    SELECT COUNT(TT2.Category_id) 
    FROM table TT2 
    WHERE TT2.Parent_id = TT1.Category_id
  ) count 
FROM table TT1
WHERE TT1.Parent_id = 185;

SQL Fiddle