Luke K Luke K - 7 days ago 7
MySQL Question

Selecting the Difference of two tables

I'm trying to do something that I feel should be somewhat simple. I have two select statements:


  1. SELECT concat(st.fname,' ',st.lname) as Fullname, st.parent_phone
    FROM student as st, grade as g, section as s, semester as sem
    WHERE st.studentid=g.studentid AND g.sectionid=s.sectionid
    AND s.semesterid=sem.semesterid AND s.semesterid not in (3, 4)
    Group by Fullname;



Which returns this (mock data):

selectstatement1


  1. SELECT concat(st.fname,' ',st.lname) as Fullname, st.parent_phone
    FROM student as st, grade as g, section as s, semester as sem
    WHERE st.studentid=g.studentid AND g.sectionid=s.sectionid
    AND s.semesterid=sem.semesterid AND s.semesterid in (3, 4)
    Group by Fullname;



Which then returns this:

enter image description here

What I'd like to do is display the three records that appear in the first statement, but not in the second statement in alphabetical order. I want to basically subtract all the records in statement 2 from statement 1. Can someone help me?

Answer

Try:

SELECT x.*
FROM (
   -- the first subquery goes here
) x
LEFT JOIN (
   -- the second subquery goes here
) y
ON x.full_name = y.full_name AND x.parent_phone = y.parent_phone
WHERE y.parent_phone IS NULL