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?

TIA,
Nick.

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)

The article is here: http://dev.mysql.com/tech-resources/...ubqueries.html

Thanks,
Nick.

SELECT DISTINCT profile.id,profile.name
FROM profile AS p INNER JOIN images AS i ON i.user_id = p.id

Thanks. That looks much simpler.

Possible alternative :
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

There may be some optimisation to be gained from reversing the where clause
i.e.
... WHERE 1 <= (SELECT COUNT(*) FROM images WHERE user_id = p.id)

That looks very useful indeed. I have made a note of it.

Thank you.

if efficiency is the criterion, my money's on the EXISTS solution

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.

n.b.
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.

Thank you both for your kind help.

Nick.