Someone kindly poined out http://sqlzoo.net when I asked about other interactive SQL resourses.
I was moving right along and feeling pretty good about what I have learned until I hit question 3a. on http://sqlzoo.net/1b.htm.
In an attempt to get he correct answer I have been doing quite a bit of research and cannot figure it out. I may be missing something but can't find the answer on the page.
Basically I have to figure out In which years was the Physics prize awarded but no Chemistry prize. This is to be a select statement from a single table. I have been reading about recursive joins and subqueries but cannot come up with the best answer.
As always, any help that can be provided is appreciated.
n1.subject="Physics" AND n1.yr
NOT IN (
Select all distinct years where a Physics prize was awarded, but exclude the years where a Chemistry prize was awarded.
This leafs all the years where a Physics prize was awarded, but no Chemistry prize.
PS. Something tells me there is a better query (more efficient) possible to obtain the same answer, I just don't know what it is
Never exclude a chemist!
Thanks for the answer, it was very helpful.
Anyone else have any solutions?
okay, since you asked...
oh no i won't
here's my query --
HAVING COUNT(CASE WHEN subject ='Physics'
THEN 'humpty' END) > 0
AND COUNT(CASE WHEN subject ='Chemistry'
THEN 'dumpty' END) = 0
and here are the results --
Are things like this covered in your book?
Thanks a lot of the answer, it is greatly appreciated.
This is what is covered in Rudy's book.