Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Posts
    15

    Unanswered: How do I for a query to do this?

    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.

  2. #2
    Join Date
    Jan 2005
    Posts
    15
    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT DISTINCT profile.id,profile.name
    FROM profile AS p INNER JOIN images AS i ON i.user_id = p.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2005
    Posts
    15
    Thanks. That looks much simpler.

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Possible alternative :
    Code:
    SELECT p.id
           , p.name
    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
    Last edited by aschk; 07-09-07 at 05:17.

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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)

  7. #7
    Join Date
    Jan 2005
    Posts
    15
    That looks very useful indeed. I have made a note of it.

    Thank you.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if efficiency is the criterion, my money's on the EXISTS solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  10. #10
    Join Date
    Jan 2005
    Posts
    15
    Thank you both for your kind help.

    Nick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •