MySQL Left Join question

Results 1 to 2 of 2

Thread: MySQL Left Join question

  1. #1
    Join Date
    Dec 1969

    Default MySQL Left Join question

    Here&#039;s my problem,<BR><BR>I have 3 (MySQL) tables:<BR><BR>appointments,<BR>teacher,<BR>users< BR><BR>In the appointments table I hold a list of appointments, into which goes:<BR><BR>fld_id &#039;autonumber id<BR>fld_time &#039;time of appt<BR>fld_date &#039;date of appt<BR>fld_t &#039;teacher allocated to appointment<BR>fld_p &#039;pupil allocated to appointment (if any, as sometimes a teacher can be allocated to a slot, but no pupil has yet booked it)<BR><BR>if a pupil books a lesson, then the fld_p field in the appointments table gets updated with the id of the pupil who has booked the lesson<BR><BR>so I want to trawl through appointments for any given day, and list the teacher name and pupil name if there is one.<BR><BR>I tried this SQL first, but it only returns rows from the appointments table where the fld_p is filled in, instead of rows where the fld_p is NULL as well:<BR><BR>select <BR>teachers.uname, <BR>users.uname, <BR>appointments.fld_id, <BR>appointments.fld_p, <BR>appointments.fld_date, <BR>appointments.fld_cat, <BR>appointments.fld_t <BR>from appointments, teachers, users<BR>where appointments.fld_t = teachers.uid AND appointments.fld_p = users.uid <BR>AND fld_date = &#039;2004-02-23&#039;;<BR><BR>then I looked at the LEFT JOIN, (I read the notes in the manual)<BR><BR>select <BR>teachers.uname, <BR>users.uname, <BR>appointments.fld_id, <BR>appointments.fld_p, <BR>appointments.fld_date, <BR>appointments.fld_cat, <BR>appointments.fld_t <BR>from appointments, teachers<BR>LEFT JOIN users<BR>ON <BR>(appointments.fld_t = teachers.uid)<BR>where appointments.fld_p = users.uid<BR>AND fld_date = &#039;2004-02-23&#039; <BR><BR>but this again missed out rows in the appointment table which contain a NULL entry for the fld_p column<BR><BR>any help most gratefully received!<BR><BR>Thanks<BR><BR>Jim

  2. #2
    Join Date
    Dec 1969

    Default RE: MySQL Left Join question

    LEFT OUTER JOIN ?<BR><BR>not sure about the MySQL implementation of this, but I&#039;d be explicit about the join type.<BR><BR><BR>j<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts