CODEWITHSUNDEEP
mysql
Manish Malviya
Manish Malviya

Reputation: 871

how to use sql join in mysql

table name: tbl_schedule

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

Table name: tbl_user

mr_id(P.K.)  mr_fname      mr_lname     
-----------  -----------   --------  
23            Manish        malviya    
24            chandan       gerry    
22            jacky         chen

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

Upvotes: 1

Views: 94

Answers (4)

Mohammedshafeek C S
Mohammedshafeek C S

Reputation: 1943

First of all u make sure that you have defined sch_date as DATE type AND Database engine should be InnoDb

then it is easy to fetch data as per your question

QUERY

SELECT T2.mr_id,T2.mr_fname,T2.mr_lname,COUNT(doctor_id) FROM tbl_schedule AS T1 INNER JOIN tbl_user AS T2 ON T1.mr_id = T2.mr_id WHERE T1.sch_date > 'date1' AND T1.sch_date < 'date2' GROUP BY doctor_id HAVING COUNT(doctor_id) > 0

Upvotes: 0

Gohn67
Gohn67

Reputation: 10638

    select tu.mr_fname, tu.mr_lname, count(ts.mr_id) as `count` 
from tbl_user tu 
inner join tbl_schedule ts on ts.mr_id = tu.mr_id and 
      ts.sch_date between '2012-01-01' and '2012-08-01' 
group by tu.mr_id

The above will get you all the users with a row in the schedule it. So the users with a count of 0 will not show up.

select tu.mr_fname, tu.mr_lname, count(ts.mr_id) as `count` 
from tbl_user tu 
left join tbl_schedule ts on ts.mr_id = tu.mr_id and 
      ts.sch_date between '2012-01-01' and '2012-08-01' 
group by tu.mr_id

This query will select the first name and last name of the user and count the number of times that user id appears in the schedule table.

It does this by grouping by the user id.

And additionally it uses a left join based on user id and date. It is important to put the date condition here so that all users are selected. This is because the left join will include all users that did not match too. But if you put this on the where clause, all users would not be selected. In other words, you will not get a count of zero for 'gerry chandan' if you put the date condition in the where clause. Instead he would be left off the results.

Upvotes: 1

Schalk
Schalk

Reputation: 142

You can try this

SELECT b.mr_id, b.mr_fname, b.mr_lname, 
     (SELECT COUNT(*) FROM tbl_schedule a WHERE (a.my_id = b.my_id)
     AND (Date >= '2012-04-01') AND (Date <= '2012-04-20')) AS COUNT
FROM tbl_user

You do not need to INNER JOIN, because it seems like you want to include ALL of the users in the tbl_users table.

Edit: You added later that you do not want a count of 0

SELECT b.mr_id, b.mr_fname, b.mr_lname, COUNT(*)
FROM tbl_schedule a 
INNER JOIN tbl_users b ON a.tr_id = b.mr_id
WHERE (Date >= '2012-04-01') AND (Date <= '2012-04-20')
GROUP BY b.mr_id, b.mr_fname, b.mr_lname

Upvotes: 0

Michael
Michael

Reputation: 12802

This should be what you need:

SELECT
  mr_fname,
  mr_lname,
  COUNT(DISTINCT tr_id)
FROM
  tbl_user
LEFT JOIN
  tbl_schedule USING (mr_id)
GROUP BY
  mr_id

Upvotes: 0

Related Questions