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
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
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
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 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
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?
[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 The profiel_id in the where clause was ambiguous. I didn’t use the alias
[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