SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: More SQL help.

  1. #1
    SitePoint Zealot
    Join Date
    Feb 2001
    Location
    Kansas City, MO
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    More SQL help.

    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.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Code SQL:
    SELECT DISTINCT
      yr
    FROM
      nobel n1
    WHERE
      n1.subject="Physics" AND n1.yr
      NOT IN (
        SELECT DISTINCT 
          yr 
        FROM
         nobel n2
        WHERE
          n2.subject="Chemistry"
    )

    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
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Never exclude a chemist!

    Jeez.

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2001
    Location
    Kansas City, MO
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Code SQL:
    SELECT DISTINCT
      yr
    FROM
      nobel n1
    WHERE
      n1.subject="Physics" AND n1.yr
      NOT IN (
        SELECT DISTINCT 
          yr 
        FROM
         nobel n2
        WHERE
          n2.subject="Chemistry"
    )

    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
    Thanks for the answer, it was very helpful.

    Anyone else have any solutions?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Andwise View Post
    Anyone else have any solutions?
    okay, since you asked...



    Quote Originally Posted by sqlzoo
    3a. In which years was the Physics prize awarded but no Chemistry prize. (WARNING - this question is way too hard for this level, you will need to use sub queries or joins).
    oh no i won't

    here's my query --
    Code:
    SELECT yr
      FROM nobel
    GROUP
        BY yr
    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 --
    Quote Originally Posted by sqlzoo
    Well Done, that is correct.
    yr
    1917
    1919
    1924
    1933
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2001
    Location
    Kansas City, MO
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, since you asked...



    oh no i won't

    here's my query --
    Code:
    SELECT yr
      FROM nobel
    GROUP
        BY yr
    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.

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    This is what is covered in Rudy's book.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •