Tricky SQL Join Query

join, mysql, sql

Solution

SELECT cr.Certificate FROM CertReqs cr
WHERE cr.Certificate NOT IN (
    SELECT sc.Certificate FROM StudentCerts sc
    WHERE sc.Email = 'This@that.com'
);

The query pretty much reads as the requirement: "I want all CertReqs, for which the student is NOT IN".

Problem

I have two tables, One called StudentCerts containing an email (the primary key) and a certificate, and another called CertReqs, containing a Certificate and Course. Part of the table could look as such: ``` StudentCerts: CertReqs: Email Certificate Certificate Course This@that.com Programmer Programmer CS 101 This@that.com English Programmer CS 202 A@B.com Econ Programmer CS 303 john@smith.com Programmer English ENG 101 English ENG 102 Econ ECON 102 Econ ECON 304 Art Art 101 Art Art 102 Journalism J 101 Journalism J 202 ``` what I am trying to do is get all of the certificates that a particular student is not a part of. For example, This@that.com is enrolled in both the Programmer and English certificate, And I would like to get an SQL statement that would return me all of the certificates in CertReqs that the particular student is not enrolled in. So for this example it should return Econ, Art, and Journalism. Ive been struggling to get this for some time, so any help would be greatly appreciated!!

Original source