JOIN with limit 1 for second table

Databases
1

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

2

Use DISTINCT.

Something like this:

SELECT DISTINCT a.valueA, c.valueC
FROM C
INNER JOIN B ON B.lookupC = C.id
INNER JOIN A ON A.lookupB = B.id
ORDER BY a.valueA, c.valueC

3

DISTINCT isn’t going to do it

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

4

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

5

@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

6

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.

7

SELECT m.id , m.name , mp.photo 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 = m.id INNER JOIN member_photos AS mp ON mp.m_id = mx.m_id AND mp.photo_id = mx.latest

8

Hi Rudy.

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

SELECT P.gebruikersnaam
     , PP.photo
  FROM profielen P
 INNER
  JOIN (SELECT profiel_id
             , MAX(photo_id) AS  latest
          FROM profiel_photos
         GROUP
            BY profiel_id ) AS PX
    ON PX.profiel_id = P.profiel_id
 INNER
  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?

9

[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

10

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

11

[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

12

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:

13

[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