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 4 of 4
  1. #1
    New Coder
    Join Date
    Sep 2012
    Posts
    32
    Thanks
    25
    Thanked 0 Times in 0 Posts

    Never display value that fails argument

    Hey all my assignment was to list the names of all animals that do not eat dog food.

    Here's what I have:
    Code:
    select A.A_Name
    	from Animal A inner join AnimalFood AF
    	on A.A_ID = AF.A_ID
    	where AF.Food != 'dog food'
    The problem is that some animals eat dog food as well as other things. So when they eat dog food the animal name (A_Name) doesn't show up but the times they eat other food they do show up.

    How do I make it so if they ever eat dog food they never show up?

    Here's the result of what I get as of now:
    Code:
    Boy
    Boy
    Boy
    Boy
    Olga
    Olga
    Olga
    Olga
    Olga
    Natasha
    Igor
    Igor
    Igor
    Tatja
    Tatja
    Tatja
    Gaia
    Gaia
    So for example, in the database Boy eats dog food so I never want him to show up.

    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Code:
    SELECT A.A_Name
    FROM Animal AS A 
    LEFT JOIN (
         SELECT A_ID FROM AnimalFood
         WHERE Food = 'dog food' ) AS ANF
    ON A.A_ID = ANF.A_ID
    WHERE ANF.A_ID IS NULL
    Last edited by Old Pedant; 10-21-2013 at 01:22 AM.
    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:

    CodyJava (10-21-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Alternative:
    Code:
    SELECT A_Name FROM ANIMAL WHERE A_ID NOT IN (
        SELECT A_ID FROM AnimalFood WHERE food = 'dog food' )
    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:

    CodyJava (10-21-2013)

  • #4
    New Coder
    Join Date
    Sep 2012
    Posts
    32
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Code:
    SELECT A.A_Name
    FROM Animal AS A 
    LEFT JOIN (
         SELECT A_ID FROM AnimalFood
         WHERE Food = 'dog food' ) AS ANF
    ON A.A_ID = ANF.A_ID
    WHERE ANF.A_ID IS NULL
    Okay thank you I'll try it tomorrow when I have access to the DB.

    Thanks again


  •  

    Posting Permissions

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