Question: 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.

Answer: 
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.
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; 

```