Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 24, 1997

Finding Exactly Two of Three -- Conditions

Question:
We are putting together a book with contributions from many Anthologies (identified by their contrnum). We want to find all Anthologies that have 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
71062.1056@compuserve.com

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date