Finding Exactly Two of Three — Conditions

Finding Exactly Two of Three — Conditions

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,        … );

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]

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of