CODEWITHSUNDEEP
mysql
rushang patel
rushang patel

Reputation: 115

Join in Mysql query giving wrong output

I have 3 tables.They are as follows.

1.system_user_master 
________________________
user_id 
user_type
user_registeration_code
user_first_name
user_last_name

2.book_appointment
-----------------------
booking_id
booking_date
puser_id
duser_id
doctor_name

3.routine_queue_patient
----------------------
queue_id
booking_id
queue_checkin_time
puser_id
duser_id
qdoctor_name

Now i want the result like 

patient_registeration_code, patient_first_name, patient_last_name, booking_date, queue_checkin_time

In routine_queue_patient booking_id can be null.I want the list of patient of current date of selected doctor who are in routine_queue_patient with booking_id has some value or can be null.if booking_id is null then it shows in booking_date in query result is null and if booking id exists in routine_queue_patient then it display's the booking date.

I have written the query.The result is as follows.

booking_date | quecheck_in_time    |  user_first_name | user_last_name | user_regis_code          
2013-11-12   | 2013-11-12 15:50:53 |  rushang                                                                                                                                  |                                                                                                     patel           |  rp9898                                   |
2013-11-12   | 2013-11-12 16:00:11 |  anjana          |     bhatt      |     ab9087

The booking_date of rushang patel must come in null as in the routine_queue_patient the booking_id of rushang patel is null but i got the booking_date of second record in front of rushang patel.

The query I have written is as follows.

SELECT DISTINCT b.booking_date
              , r.queue_checkin_time
              , s.user_first_name
              , s.user_last_name
              , s.user_registeration_code 
           FROM routine_queue_patient r 
           JOIN system_user_master s
             ON r.puser_id = s.user_id
           JOIN book_appointment b  
             ON ((b.booking_id = r.booking_id) OR r.booking_id is NULL) 
            AND DATE(r.queue_checkin_time) = '2013-11-12'
          WHERE r.qdoctor_name = 'kashyup Nanavati'
            AND DATE(b.booking_date) = '2013-11-12'

Thanks Rushang

Upvotes: 0

Views: 164

Answers (1)

OlivierH
OlivierH

Reputation: 3890

The join with book_appointment table is wrong. You shouldn't try to join on a null value. Use left join to do it : it will join if a correspondant row is found, else all joined table columns will be null.

=>

   SELECT DISTINCT b.booking_date
          , r.queue_checkin_time
          , s.user_first_name
          , s.user_last_name
          , s.user_registeration_code 
       FROM routine_queue_patient r 
       JOIN system_user_master s
         ON r.puser_id = s.user_id
  LEFT JOIN book_appointment b  
         ON (b.booking_id = r.booking_id AND DATE(b.booking_date) = '2013-11-12')
      WHERE r.qdoctor_name = 'kashyup Nanavati'
        AND DATE(r.queue_checkin_time) = '2013-11-12'

Upvotes: 1

Related Questions