JOIN with limit 1 for second table

I have a members table and a member_photos table. Each member has multiple photos, but for their profile I just need 1 photo. How would I do in a join?

Thank you in advance


Something like this:

SELECT DISTINCT a.valueA, c.valueC
ORDER BY a.valueA, c.valueC

DISTINCT isn’t going to do it

donboe, which photo do you want? you have to be able to identify it in some way

Is the photo for use as a member’s avatar?

@Rudy It would be nice if it could be a random one but that is not necessary, It is ok when it is the highest or lowes photo_id as well.

@SpacePhoenix Yes something like that

I would use a flag field or a separate table of profile photos to set and later determine which photo is profile photo. It could default to the first photo but it should give the members the option to change it later.

SELECT , , FROM members AS m INNER JOIN ( SELECT m_id , MAX(photo_id) AS latest FROM member_photos GROUP BY m_id ) AS mx ON mx.m_id = INNER JOIN member_photos AS mp ON mp.m_id = mx.m_id AND mp.photo_id = mx.latest

Hi Rudy.

I used your query with the field and table names as I have them:

SELECT P.gebruikersnaam
  FROM profielen P
  JOIN (SELECT profiel_id
             , MAX(photo_id) AS  latest
          FROM profiel_photos
            BY profiel_id ) AS PX
    ON PX.profiel_id = P.profiel_id
  JOIN profiel_photos AS PP
    ON PP.profiel_id = PX.profiel_id
   AND PP.photo_id = PX.latest

But I don’t get any results?

[quote=“donboe, post:8, topic:99144”]But I don’t get any results?[/quote]run the subquery by itself to see what it produces

also, remember to test all your queries outside of php first

I found the problem Rudy it had to do with the where clause. Thanks a lot for the help

[quote=“donboe, post:10, topic:99144”]
I found the problem Rudy it had to do with the where clause. Thanks a lot for the help
[/quote]WHERE clause? there was no WHERE clause

you are hiding stuff from me again LOL

Hahaha, I would not dare to hide stuff from you Rudy :blush: The profiel_id in the where clause was ambiguous. I didn’t use the alias :frowning:

[quote=“donboe, post:1, topic:99144, full:true”]The profiel_id in the where clause was ambiguous[/quote]let me say this again… there is/was no WHERE clause