1

I wasn't sure what to title this question, so I apologize if the title is misleading.

I have two columns in a table MovieID and Format (I have more, but these are the ones i am focusing on.

There are two types of values in the 'Format' column: 'DVD' and 'Blu-ray'. I want to select movies that ONLY have the DVD format. Note that there are multiple movies that have the Blu-ray and DVD format, I do not want to display these values. See the Fiddle below for a sample of my data. Thank you!!!

http://sqlfiddle.com/#!2

MovieID | Format
-----------------
1000    DVD
1000    DVD
1000    Blu-ray
1001    DVD
1001    DVD
1002    DVD
1003    DVD
1003    Blu-ray
1004    DVD

I WANT TO OUTPUT

MovieID
------------
1001    
1002     
1004
Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
user1330649
  • 79
  • 5
  • The link to SQLFiddle does not work :( – Sergey Kalinichenko Aug 20 '13 at 00:37
  • 1
    You've changed the question so much that the current answers no longer make sense. It would be better to write a new one instead of replacing the old one. It's not nice to the people who answered your original question. – xwoker Sep 12 '13 at 07:53
  • If you have a new question, please post it as a new one. Removing the previously accepted answer and then rewriting the original question from scratch is just vandalism and causes a lot of confusion (I just got the notification that I lost rep over my accepted answer being removed). – Niels Keurentjes Sep 12 '13 at 10:38

2 Answers2

2

One straightforward way to accomplish this is as follows:

SELECT DISTINCT (MovieID)
FROM Movies m
WHERE Format='DVD'
AND NOT EXISTS (
    SELECT * FROM Movies mm WHERE mm.MovieID=m.MovieID AND mm.Format='Blu-ray'
)

This is pretty much a translation of English description of the problem to SQL syntax.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
1
select MovieID
from Table
where Format = 'DVD'
  and MovieID not in (select MovieID from Table where Format <> 'DVD')

Don't worry if it seems inefficient, if you have proper indexes a good DBMS will optimize this into highly efficient code.

Niels Keurentjes
  • 41,402
  • 9
  • 98
  • 136