mysqlsqldatabasemysql++

Displaying the records not present in the second table in MySQL+

SELECT C.COURSE_ID, TITLE, A.STUDENT_ID, COMPANY_ID, AD.ADDRESS
FROM COURSE C, STUDENT S, ADDRESS AD, ATTENDANCE A
WHERE C.COURSE_ID = AD.COURSE_ID
AND AD.ADDRESS = A.ADDRESS
AND A.STUDENT_ID = S.STUDENT_ID
AND COMPANY_ID IS NOT NULL;

I have got 4 tables and this query does all it needs to except that i have to include the ADDRESS not present in the Attendance table. For example

ADDRESS TABLe          ATTENDANCE table
Address                  Address
  a                         a
  b                         b
  c
  d

So i want the end result to show:

COURSE ID    TITLE   STUDENT_ID   COMPANT_ID   ADDRESS
   BLA        BLA      BLA         BLA           a
   BLA        BLA      BLA         BLA           b
   BLA        BLA      BLA         BLA           c
   BLA        BLA      BLA         BLA           d

While the query above shows the same result but excluding c and d present in 'address' column.

Sorry if i'm not clear enough. Thank you

Solution

  • If you change your query to use an LEFT JOIN to join the ATTENDANCE table it will exclude all records not present in it that are in the ADDRESS table