andwise — 2010-02-12T15:07:26-05:00 — #1
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.
scallioxtx — 2010-02-12T17:47:08-05:00 — #2
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
dr_john — 2010-02-12T17:49:11-05:00 — #3
Never exclude a chemist!
andwise — 2010-02-13T15:04:11-05:00 — #4
Thanks for the answer, it was very helpful.
Anyone else have any solutions?
r937 — 2010-02-13T18:46:02-05:00 — #5
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 --
andwise — 2010-02-13T20:20:40-05:00 — #6
Are things like this covered in your book?
Thanks a lot of the answer, it is greatly appreciated.
spacephoenix — 2010-02-13T20:39:24-05:00 — #7
This is what is covered in Rudy's book.