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:
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!