Question:
We are putting together a book with contributions from many Anthologies (identified by their contrnum). We want to find all Anthologies thathave articles in exactly two out of three categories in the book for a specified set of three categories that we put into the query as parameters.

    CREATE TABLE Anthology        (contrnum INTEGER NOT NULL,        contributor CHAR(20) NOT NULL,        category INTEGER NOT NULL,        … );

Answer:
One reason this is a hard problem is that we are using parameters and not another table to get the categories we are searching for. Another problem with the specification is that it does not tell if we want any two of the three categories, or if we want a particular order (that is, category one and category two, but not category three). The latter is actually easy to do:

    SELECT A1.contrnum, A1.category, A2.category        FROM Anthology AS A1,            Anthology AS A2        WHERE A1.contrnum = A2.contrnum  — self join table             AND A1.category = :1st_category — category #1 first            AND A2.category = :2nd_category — category #2 second            AND NOT EXISTS (SELECT *      — but no category #3 anywhere                FROM Anthology AS A3                WHERE A1.contrnum = A3.contrnum                      AND A3.category = :3rd_category));

But the query to find any two out of three has to rely on some tricky coding. This answer will not tell you which two of the three is missing, however:

    SELECT contrnum, contributor, :1st_cat, :2nd_cat, :3rd_cat        FROM Anthology AS A1,        WHERE A1.category IN (:1st_cat, :2nd_cat, :3rd_cat)            AND EXISTS                 (SELECT *                    FROM Anthology AS A2,            WHERE A2.category IN (:1st_cat, :2nd_cat, :3rd_cat)                AND A1.category < A2.category                AND A1.contrnum = A2.contrnum                 AND NOT EXISTS                     (SELECT *                        FROM Anthology AS A3,                        WHERE A3.category                             IN (:1st_cat, :2nd_cat, :3rd_cat)                        AND A1.contrnum = A3.contrnum                         AND (A1.category <> A3.category                         OR A2.category <> A3.category)));

To find the contributors who have something in all three categories, change the NOT EXISTS to EXISTS.

To find the contributors who have only one category:

    SELECT contrnum, contributor, :1st_cat        FROM Anthology AS A1,    WHERE A1.category = :1st_cat        AND NOT EXISTS (SELECT *            FROM Anthology AS A2            WHERE A2.category = :1st_cat                AND A1.contrnum = A2.contrnum                AND A1.category <> A2.category);

which is a “collapsed version” of the two out of three queries..

Puzzle provided courtesy of:
Joe Celko
[email protected]