I have a table containing people stuff and another table containing images of those people. One person can have up to four images. I would like to select all people who have at least one image. I only want one record row per person in the result.
How would I go about constructing a select to do this?
Sorry to ask for help before I have properly researched but I didn't think I would locate a solution before it got answered here. I located a useful article which basically gave me the following solution:
SELECT profile.id,profile.name FROM AS p
WHERE EXISTS (SELECT * FROM images AS i WHERE p.id = i.user_id)
FROM profile p
WHERE (SELECT COUNT(*) FROM images WHERE user_id = p.id) >= 1
The advantage of the solution provided above is that you can now test for other numbers of images.
i.e. replace the 1 with 2 to get how many profiles have 2 or more images.
Or change the sign to = to get the profiles that have EXACTLY that number of images.
e.g. = 2
Yes quite possibly, however if you progress further and end up specifying other criteria for your select statement (in the WHERE clause) you might find that a dependent subquery will be more efficient, especially if your table has grown to a very large number of records. It's by the by really. All of the above solutions will do for what you are after.
I'm a fan of the EXISTS or IN (non-dependent subquery) especially if it's for a small number of records. As rudy says, it's probably more efficient in this case.