Question: > 
> **Possible Duplicate:**  
> 
> [how to use sql join in mysql](https://stackoverflow.com/questions/9993227/how-to-use-sql-join-in-mysql) 
> 
> 
> 

table name: tbl\_schedule

```
tr_id(P.K.)  mr_id(F.K.)   sch_date   doctor_id   
-----------  -----------   --------   ----------
1              2         01/01/2012    32
2              2         05/01/2012    13
3              4         08/01/2012    14

```

Table name: tbl\_user

```
mr_id(P.K.)  mr_fname      mr_lname     
-----------  -----------   --------  
2            Manish        malviya    
3            chandan       gerry    
4            jacky         chen 
5            raza          abbas

```

Please reply with query thanks
i want to get number of mr between two dates with `mr_fname, mr_lname group by mr_id`
It should look like. in this counting is from tbl\_schedule table and mr\_fnmae and mr\_lname are fetched from tbl\_user with reference of mr\_id. 

one more thing i dont want 0 count

```
mr_fname      mr_lname   count  
-----------   --------  -------
Manish        malviya    2
jacky         chen       1

```

Answer: 
You want to write a `SELECT`: <http://dev.mysql.com/doc/refman/5.0/en/select.html>

In your select you are looking to `JOIN` the two tables:
<http://dev.mysql.com/doc/refman/5.0/en/join.html>

Finally you want to use the `COUNT()`- aggregate function in conjunction with a `GROUP BY` (on user id):
<http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html>
```
SELECT
  mr_fname,
  mr_lname,
  COUNT(DISTINCT tr_id)
FROM
  tbl_user
INNER JOIN
  tbl_schedule USING (mr_id)
GROUP BY
  mr_id

```