www.webdeveloper.com
Results 1 to 5 of 5

Thread: Searching link tables

  1. #1
    Join Date
    Dec 2007
    Posts
    95

    Searching link tables

    I have a link table that contains meals, with their ingredients.

    For example:

    beans on toast (mealID 1):

    beans (ingredientID 1)
    bread (ingredientID 2)
    butter (ingredientID 3)

    The table looks like the following
    Code:
    mealID  ingredientID
    1         1                   
    1         2
    1         3
    What SQL statement can I use to return any meals that do not contain a specific ingredient?

    So if for example I wanted to return meals that do not contain butter (ingredientID 3), what do I do? At the moment, the meal is still returned because it is returning the result from a tuple that is not butter, i.e. bread of beans.

    Thanks,
    TK

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    maybe...

    Code:
    select * from meals
    where mealID not in (select mealID from meal_ingredients where ingredientID=3)

  3. #3
    Join Date
    Dec 2007
    Posts
    95
    Quote Originally Posted by chazzy
    maybe...

    Code:
    select * from meals
    where mealID not in (select mealID from meal_ingredients where ingredientID=3)
    Thanks.

    But how do I do that, if there are many ingredientIDs that I want to make sure are not in the result set?

    At the moment I have a very large array with ingredientIDs that I want to exclude from the meal results.

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    change ingredientID=3 to ingredientID in (list of values), where you programmaticly create the list of values as a comma separated list.

  5. #5
    Join Date
    Dec 2007
    Posts
    95
    Quote Originally Posted by chazzy
    change ingredientID=3 to ingredientID in (list of values), where you programmaticly create the list of values as a comma separated list.
    Nicely done.

    Thanks once again

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles