I have two tables of data in a database: "property" (which stores houses) and "photo" (which stores URLs of images). There are multiple records in the property table, each representing a different house for sale with a unique PropertyID. Each house can have as many different photos as required, so in the photos table, each record references which PropertyID it is associated with. (say for example, a particular house might have 4 photos associated with it). This is made more complicated by the fact that each photo can be either "priority" or not, indicating whether it is the most "important" photo of that property.
My question is this: when doing a search on the database, I'd like it to be able to return back the data for each property, but also return a single image URL (the "priority" image, if one has been selected, or any other image at random if not). I'd like it to be able to do this using just one query, so I can have 10 results appearing on one page, with all the required fields from property and an extra field on the end, "Photo", which will give the URL of the image that needs to be loaded. An extra consideration in this is that some properties may not have photos associated with them.
At the moment my only workaround is to do the query to retrieve all 10 results and, then on displaying each result, perform a separate query to retrieve one photo URL from the photo table. This is clumsy, slow and bad form, IMHO.
I'm sorry, I don't know the exact name of what I'm trying to achieve, but if anybody could help me that would be great. 🙂
If I haven't explained myself very well then please post a follow-up. Thanx.
