I'm trying to find a patient's appointments and messages count. My table records are like below 3 table patient, appointments and messages
Patient table
pid fname lname
1 john sid
2 rother ford
3 megan rough
4 louis kane
appointments table
id pid appointment_date
1 1 2015-08-04
2 2 2015-08-05
3 1 2015-08-06
4 1 2015-08-07
5 3 2015-08-07
6 2 2015-08-08
7 4 2015-08-13
8 1 2015-08-12
Messages table
id pid description message_date
1 2 join 2015-08-04
2 2 update 2015-08-05
3 3 join 2015-08-05
4 4 update 2015-08-10
5 3 test 2015-08-07
So if write query to find counts i'm getting wrong values
SELECT pd.fname,pd.lname , pd.pid, COUNT( a.id ) AS app_cnt, COUNT( m.id ) AS mes_cnt
FROM patient pd
LEFT OUTER JOIN appointments a ON a.pid = pd.pid
LEFT OUTER JOIN messages m ON m.pid = pd.pid
GROUP BY pd.pid
ORDER BY pd.pid
fname lname pid app_cnt mes_cnt
john sid 1 4 0
rother ford 2 4 4
megan rough 3 2 2
louis kane 4 1 1
Here pid 1 have 4 appointments and 0 messages, pid 2 have 2 appointments and 2 messages but getting wrong values.
Can someone please help to resolve this issue. I'm not interested in writing sub queries for this.
Functionality looks simple but I'm really facing problem for writing query.
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire