List distinct values based on a condition

Question

List Distinct Value in Alphabet Column where all Alphabets that has RID 101 only Shall be eliminated from the list.

RID	Alphabet
100	A
101	B
102	B
101	C
104	D
101	D
106	E
102	F
108	C
108	A
104	E

Output Column shall be

Alphabet
A
E
F

Since A, B, & C has RID 101 and should be eliminated.

How shall I achieve This? I have tried to use Group by with having function? And Where exists but did not work for me? Appreciate your help

Answer 1

try this

SELECT Alphabet FROM `mytable` WHERE Alphabet NOT IN (
  SELECT Alphabet FROM `mytable` WHERE RID = 101
)

Based on your new info your solution might be a performance killer because of using HAVING MAX(when case...). But with Rating.rID as index the solution below will work very well:

Select Movie.title From Movie
  Left Join Rating on Rating.mID = Movie.mID
  Left Join Reviewer on Rating.rID = Reviewer.rID
  WHERE Movie.mID NOT IN (
    SELECT Rating.mID FROM Rating WHERE Rating.rID = 205
  )
GROUP BY Movie.mID