CODEWITHSUNDEEP
mysql
Josef
Josef

Reputation: 2726

Get number of records in child table

I need to get number of record in child table.

Parent table 

+-------------+------------+  
| Id          | Name       |  
+-------------+------------+  
| 1           | a          |  
+-------------+------------+    
| 2           | b          |    
+-------------+------------+ 
| 3           | c          | 
+-------------+------------+

Child table: 

+-------------+------------+  
| Id          | Code       |  
+-------------+------------+
| 1           | d1         |  
+-------------+------------+ 
| 1           | d2         | 
+-------------+------------+    
| 1           | d3         |  
+-------------+------------+ 
| 1           | d1         | 
+-------------+------------+    
| 2           | d2         |  
+-------------+------------+ 
| 2           | d3         | 
+-------------+------------+ 
| 3           | d1         |  
+-------------+------------+ 
| 3           | d2         | 
+-------+-----+------------+

Result should like:

+-------------+------------+------------+ 
| Id          | Name       | NumOfRec   |  
+-------------+------------+------------+ 
| 1           | a          |        4   |
+-------------+------------+------------+  
| 2           | b          |        2   |
+-------------+------------+------------+  
| 3           | c          |        2   |
+-------------+------------+ -----------+

I've tried with COUNT but then I get only number of records for one id but not for other ID's.

Upvotes: 3

Views: 3557

Answers (4)

Jeevan Roy dsouza
Jeevan Roy dsouza

Reputation: 673

Use this code. Writing query with count will give you solutions 

select distinct a.id,count(b.id) from parent a, child b where a.id=b.id;

Upvotes: 0

VMai
VMai

Reputation: 10336

Use the GROUP BY clause to group the result by every distinct value of id. This should do it

To get the edited result, you've got to join the parent table.

SELECT 
    parent.id,
    parent.Name, 
    parent.other_columns_that_you_need,
    COUNT(id) as NumOfRec 
FROM 
    child 
INNER JOIN
    parent
ON
    parent.id = child.id
GROUP BY 
    parent.id

Note

In standard SQL you will have to group by all columns without an aggregate function. MySQL has got extended the use of GROUP BY and you can omit those columns with the same values. This is here valid, because all not aggregated columns are directly dependent of the parent id.

Upvotes: 2

ngrashia
ngrashia

Reputation: 9904

This should do it:

SELECT ID, COUNT(ID) AS RECCOUNT FROM CHILD_TABLE
GROUP BY ID;

To get the records based on PARENT ID, you can do,

SELECT P.ID, P.NAME, COUNT(C.ID) FROM
PARENT P LEFT OUTER JOIN CHILD C
ON P.ID = C.ID
GROUP BY P.ID, P.NAME;

SQL FIDDLE HERE : http://sqlfiddle.com/#!8/08d58/3

Left outer join results in number of records as 0 for parent ids not having corresponding child records.

Upvotes: 5

Jens
Jens

Reputation: 69440

use group by 

select count(*) as NumOfRec  , id from <Child table> group by id;

For your updated question, this should work:

select parent.id,parent.name,numOdRec from parent join (
    select count(*) as NumOfRec  , id from <Child table> group by id) as childs on childs.id=parent.id

Upvotes: 1

Related Questions