CODEWITHSUNDEEP
mysqlsqljoin
user1875195
user1875195

Reputation: 988

Tricky SQL Join Query

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
[email protected]      Programmer            Programmer           CS 101
[email protected]      English               Programmer           CS 202
[email protected]            Econ                  Programmer           CS 303
[email protected]     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, [email protected] 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!!

Upvotes: 5

Views: 1273

Answers (5)

Abhi
Abhi

Reputation: 1115

SELECT Certificate FROM `CertReqs` 

WHERE Certificate NOT in(
    SELECT Certificate 
    FROM `StudentCerts` 
    WHERE Email  = '[email protected]' 
)

Upvotes: 0

Amit Dhiman
Amit Dhiman

Reputation: 1

SELECT DISTINCT A.* fROM 
(
SELECT DISTINCT EMAIL, CERTIFICATE fROM #StudentCerts A
CROSS JOIN #CertReqs B)  A
LEFT JOIN #StudentCerts B ON A.EmaiL = B.EMAIL AND B.CERTIFICATES = A.Certificate
WHERE B.EMAIL IS NULL
ORDER BY A.EMAIL

Upvotes: 0

Ja͢ck
Ja͢ck

Reputation: 173662

A little late in the game, but a simple LEFT JOIN solves this problem quite well:

SELECT DISTINCT cr.Certificate 
FROM CertReqs cr
LEFT JOIN StudentCerts sc ON cr.Certificate = sc.Certificate AND sc.Email = '[email protected]'
WHERE sc.Email IS NULL

Upvotes: 1

John Woo
John Woo

Reputation: 263933

This will list all student lacking for a certain certificate

SELECT  DISTINCT x.*
FROM
    (
        SELECT  a.Email, b.Certificate
        FROM    (SELECT DISTINCT Email FROM StudentCerts) a
                CROSS JOIN
                (SELECT DISTINCT Certificate FROM CertReqs) b
    ) x LEFT JOIN studentCerts y
        ON x.Email = y.Email AND
            x.Certificate = y.Certificate
WHERE  y.Email IS NULL
ORDER BY x.EMAIL

if you want for specific email then you only need to add extra condition

SELECT  DISTINCT x.*
FROM
    (
        SELECT  a.Email, b.Certificate
        FROM    (SELECT DISTINCT Email FROM StudentCerts) a
                CROSS JOIN
                (SELECT DISTINCT Certificate FROM CertReqs) b
    ) x LEFT JOIN studentCerts y
        ON x.Email = y.Email AND
            x.Certificate = y.Certificate
WHERE  y.Email IS NULL AND
       x.Email = '[email protected]'
ORDER BY x.EMAIL

Upvotes: 0

Emil Ivanov
Emil Ivanov

Reputation: 37673

SELECT cr.Certificate FROM CertReqs cr
WHERE cr.Certificate NOT IN (
    SELECT sc.Certificate FROM StudentCerts sc
    WHERE sc.Email = '[email protected]'
);

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

Upvotes: 2

Related Questions