CODEWITHSUNDEEP
sqlexists
Streamer
Streamer

Reputation: 173

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. 

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?

Upvotes: 0

Views: 55

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions