-5

Display the details of the employees who have subscribed for Football and Chess but not for Tennis.

SELECT *
FROM   employee
WHERE  empid IN (SELECT empid
                 FROM   subscription
                 WHERE  facid IN (SELECT facid
                                  FROM   facility
                                  WHERE  facility = 'Chess'
                                          OR facility = 'Football'))
       AND empid NOT IN (SELECT empid
                         FROM   subscription
                         WHERE  facid = (SELECT facid
                                         FROM   facility
                                         WHERE  facility = 'Tennis'));

SELECT DISTINCT empid
FROM   subscription
WHERE  facid IN (SELECT facid
                 FROM   facility
                 WHERE  facility = 'Chess'
                         OR facility = 'Football')
       AND facid != (SELECT facid
                     FROM   facility
                     WHERE  facility = 'Tennis');

The first one gives correct result.

Pop Stack
  • 926
  • 4
  • 19
  • 27
  • 1
    have you heard of JOINs? – Mitch Wheat Jan 05 '12 at 09:38
  • 2
    Without seeing the data, understanding what a "correct result" is and what an "incorrect result" is, we can't help. – Oded Jan 05 '12 at 09:38
  • @Oded: Despite the absence of data, just explain how does the AND in the second query behave. – Pop Stack Jan 05 '12 at 09:44
  • Explain what you think a correct result should be. I can't guess. – Oded Jan 05 '12 at 09:45
  • @MitchWheat: have you heard of semi join and semi difference? – onedaywhen Jan 05 '12 at 09:52
  • @onedaywhen: totally irrelevent: I'm not the one asking the question. – Mitch Wheat Jan 05 '12 at 10:49
  • 3
    @MitchWheat: perhaps rhetorical but your comment *does* ask a question ;) To my eye, the OP has chosen to write a semi join using `IN` and an anti join using `NOT IN`. So please explain why you think the OP may not have heard of JOINs? Are you, for example, suggesting that writing semi join / semi difference using anything other than JOINs is questionable practice? My thinking is that if I don't understand what you are alluding to then the OP probably doesn't either. Please expand your comments to make what you are implying to more explicit. Thanks :) – onedaywhen Jan 05 '12 at 12:09

1 Answers1

5

First query: All employees that has subscribed to Chess or Football but has no subscriptions of Tennis.

Second query: All empid's that has subscribed to Chess or football.

Your second query is filtering subscriptions and one subscription can only link to one facility. That means that if you have a subscription of Chess it will of course not at the same time be a subscription for Tennis and if you find a subscription of Tennis it will already be excluded be because it is not Chess or Football. Any EmpID that subscribes to both Chess and Tennis will be included because of the subscription of Chess but it will not be excluded because of the subscription of Tennis.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • edited with corrections. now? – Pop Stack Jan 05 '12 at 09:55
  • Mikael tried to explain to you that AND facid != (...) in your second query does nothing useful at all. The way you write this, means that your are trying to exclude certain facid's, but that exclusion has already been done by virtue of the facid having to be linked to either chess or football. But what you really want to exclude, are employees. So for your query to achieve this, it must necessarily contain something that behaves like "AND empid NOT IN (...)". NOT "AND facid NOT IN (...)". – Erwin Smout Jan 06 '12 at 13:18
  • @Erwin - I have changed the answer since that comment and I am sure Mr Pop knows the difference between the queries now since he has accepted the answer. – Mikael Eriksson Jan 06 '12 at 13:34
  • Ah okay. All three appear as 'yesterday' on my screen though ... Hard to tell the order from that ... – Erwin Smout Jan 06 '12 at 19:25