Azurasky Azurasky - 1 month ago 11
SQL Question

comparing composite keys in sql

I can't seem to figure out how to write this. I have four tables: a master list of awards, and then awards that are actually given out to actors, producers, and TV shows. The latter group are identifiable by a composite key. I want to list all awards that were not given out. Here's what I have done so far.


  • award pk: {award_name, award_type, award_year}

  • actoraward pk: {award_name, award_type, award_year} fk: {award_name, award_type, award_year}

  • prodaward pk: {award_name, award_type, award_year} fk: {award_name, award_type, award_year}

  • showaward pk: {award_name, award_type, award_year} fk: {award_name, award_type, award_year}



SELECT AWARD.AWARD_NAME, AWARD.AWARD_TYPE, AWARD.AWARD_YEAR
FROM AWARD
WHERE NOT EXISTS
(
SELECT *
FROM AWARD AW, ACTORAWARD ACAW
WHERE (AW.AWARD_NAME = ACAW.AWARD_NAME AND AW.AWARD_TYPE = ACAW.AWARD_TYPE AND AW.AWARD_YEAR = ACAW.AWARD_YEAR)
)

AND NOT EXISTS
(
SELECT *
FROM AWARD AW, PRODAWARD PRAW
WHERE (AW.AWARD_NAME = PRAW.AWARD_NAME AND AW.AWARD_TYPE = PRAW.AWARD_TYPE AND AW.AWARD_YEAR = PRAW.AWARD_YEAR)
)

AND NOT EXISTS
(
SELECT *
FROM AWARD AW, SHOWAWARD SHAW
WHERE (AW.AWARD_NAME = SHAW.AWARD_NAME AND AW.AWARD_TYPE = SHAW.AWARD_TYPE AND AW.AWARD_YEAR = SHAW.AWARD_YEAR)
)


The syntax is good so I'm not worried about that, but not exists doesn't work and I can't compare three values simultaneously using
IN
so what can I do to solve this?

Answer

You don't need the joins in the sub-selects. Use a co-related subquery instead:

SELECT aw.award_name, aw.award_type, aw.award_year
FROM award aw
WHERE NOT EXISTS (SELECT *
                  FROM actoraward acaw
                  WHERE aw.award_name = acaw.award_name 
                    AND aw.award_type = acaw.award_type 
                    AND aw.award_year = acaw.award_year)
  AND NOT EXISTS (SELECT *
                  FROM prodaward praw
                  WHERE aw.award_name = praw.award_name 
                    AND aw.award_type = praw.award_type 
                    AND aw.award_year = praw.award_year)
  AND NOT EXISTS (SELECT *
                  FROM showaward shaw
                  WHERE aw.award_name = shaw.award_name 
                    AND aw.award_type = shaw.award_type 
                    AND aw.award_year = shaw.award_year);

and I can't compare three values simultaneously using IN

Yes, you can compare multiple values with an IN clause:

SELECT *
FROM award aw
WHERE (aw.award_name, aw.award_type, aw.award_year) 
      NOT IN (  
                SELECT acaw.award_name, acaw.award_type, acaw.award_year
                FROM actoraward acaw
                UNION 
                SELECT praw.award_name, praw.award_type, praw.award_year
                FROM prodaward praw
                UNION 
                SELECT shaw.award_name, shaw.award_type, shaw.award_year
                FROM showaward shaw
             )

That will most probably be slower then the version with EXISTS

Comments