CODEWITHSUNDEEP
mysqlsqlgroup-by
thesentyclimate413
thesentyclimate413

Reputation: 97

SQL Query EXCEPT

If I have a table set up like:

╔══════╦══════╗
║  A   ║  B   ║
╠══════╬══════╣
║ Sam  ║ sec2 ║
║ Sam  ║ sec2 ║
║ Sam  ║ sec2 ║
║ Fred ║ sec1 ║
║ Fred ║ sec2 ║
║ Fred ║ sec1 ║
║ Joe  ║ sec1 ║
║ Joe  ║ sec1 ║
╚══════╩══════╝

I want the result to be

╔═════╦══════╗
║  A  ║  B   ║
╠═════╬══════╣
║ Sam ║ sec2 ║
║ Sam ║ sec2 ║
║ Sam ║ sec2 ║
║ Joe ║ sec1 ║
║ Joe ║ sec1 ║
╚═════╩══════╝

So basically i want to query the people who are in sec1 OR sec2 but not both and I want to leave duplicates.

My attempt:

SELECT A, B
FROM TABLE
WHERE (A = 'SAM' OR A = 'FRED' OR A = 'JOE') AND NOT (B = 'sec1' AND B = 'sec2')

Upvotes: 0

Views: 241

Answers (1)

John Woo
John Woo

Reputation: 263733

Ok, This query is useful if you have other more columns on the table. What the subquery does is it separately gets the A which has only unique for B. MAX(B) = MIN(B) is the same as COUNT(DISTINCT B) = 1. The result of the subquery is then joined back on the table itself through column A.

SELECT  y.*
FROM    tableName y
        INNER JOIN
        (
            SELECT  A
            FROM    tableName
            GROUP   BY A
            HAVING  MAX(B) = MIN(B) 
                    AND MAX(B) IN ('sec1', 'sec2')
        ) x ON y.A = x.A

OR

SELECT  y.*
FROM    tableName y
        INNER JOIN
        (
            SELECT  A
            FROM    tableName
            GROUP   BY A
            HAVING  COUNT(DISTINCT B) = 1 
                    AND MAX(B) IN ('sec1', 'sec2')
        ) x ON y.A = x.A

Upvotes: 3

Related Questions