CODEWITHSUNDEEP
mysqlparent-child
Newbi
Newbi

Reputation: 109

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.

Upvotes: 0

Views: 48

Answers (2)

Kickstart
Kickstart

Reputation: 21513

Another option, avoiding using a sub query is to use a self join:-

SELECT t1.parent_id,
        t1.category_id,
        COUNT(t2.category_id)
FROM table t1
LEFT OUTER JOIN table t2 ON t1.category_id = t2.parent_id
WHERE t1.parent_id = 185
GROUP BY t1.parent_id,
        t1.category_id

This will count parent recodss who have 0 or more child records. If you only want those with 1 or more child records then change the LEFT OUTER JOIN to an INNER JOIN.

Upvotes: 0

zakhefron
zakhefron

Reputation: 1443

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

Upvotes: 2

Related Questions