Results 1 to 2 of 2

Thread: Select from two tables if data exists, else just select from one

  1. #1
    Join Date
    Jan 2009
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Select from two tables if data exists, else just select from one

    I am using phpMyAdmin and DW CS3. I am trying to combine two tables. I will be using it in a repeating region, so I don't think I can have two separate queries (right?). The database is set up as:

    -recipeID
    recipename
    (etc.)

    -photoID
    recipeID
    photosrc

    My current select statement is:
    SELECT recipe.recipeID, recipe.recipename, photos.photosrc
    FROM recipe, photos
    WHERE photos.recipeID = recipe.recipeID

    As you can see, the tables are connected using the recipeID field. This works until there is no photo for a chosen recipe. If there is no photo, the data is empty.

    I would like to be able to have it select the recipe, and if any photos exist to select those as well. If no photos exist, then just give me the recipe. I have tried using EXISTS statements, but I can't figure out how to write it correctly to this will happen.

    I have also thought about upon inserting a new recipe through a form, to have the photos table updated as well with the photosrc NULL. But I couldn't figure out how get the auto-incrementing recipeID in order to insert it into the photo table at the same time the recipe table is inserted.

    Hopefully you can make sense of this. What is the best path to take to make this work? Thank you!

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,743
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Code:
    SELECT recipe.recipeID, recipe.recipename, photos.photosrc FROM recipe LEFT JOIN photos ON people.id=notes.id
    To choose the lesser of two evils is still to choose evil. My personal site

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
  •