Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Sep 2010
    Location
    The Twilight Zone
    Posts
    86
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Short SQL Query Question

    Table name = nobel
    yr subject winner
    1960 Chemistry Willard F. Libby
    1960 Literature Saint-John Perse
    1960 Medicine Sir Frank Macfarlane Burnet
    1960 Medicine Peter Medawar

    I need to find : In which years was the Physics prize awarded but no Chemistry prize?

    Here's what I have

    Code:
    select yr from nobel where yr NOT IN(select yr from nobel where subject='Chemistry') AND subject='Physics'
    Why is it incorrect?!

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,863
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    I need to find : In which years was the Physics prize awarded but no Chemistry prize?
    There's no "Physics prize" in the any rows given?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    New Coder
    Join Date
    Sep 2010
    Location
    The Twilight Zone
    Posts
    86
    Thanks
    17
    Thanked 2 Times in 2 Posts
    I only gave a sample of the table. The table contains the winner, subject, and year. The subject columns indicate the prize.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,084
    Thanks
    80
    Thanked 4,552 Times in 4,516 Posts
    Your query worked properly for me.

    Code:
    mysql> select * from nobel;
    +------+-----------+
    | yr   | subject   |
    +------+-----------+
    | 1960 | Physics   |
    | 1960 | Chemistry |
    | 1964 | Physics   |
    | 1968 | Physics   |
    | 1968 | Chemistry |
    | 1972 | Physics   |
    +------+-----------+
    6 rows in set (0.00 sec)
    
    mysql> select yr from nobel where yr NOT IN(select yr from nobel 
    where subject='Chemistry') AND subject='Physics';
    +------+
    | yr   |
    +------+
    | 1964 |
    | 1972 |
    +------+
    2 rows in set (0.00 sec)
    Maybe your data is wrong??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Ahlahn (06-04-2011)

  • #5
    New Coder
    Join Date
    Sep 2010
    Location
    The Twilight Zone
    Posts
    86
    Thanks
    17
    Thanked 2 Times in 2 Posts
    resolved! There are still duplicate years, I needed to add distinct. Thanks for your help!


  •  

    Posting Permissions

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