I am trying to write a SQL query where it collects the User info along with UserPhotos. For some reason my query results in returning same user info with a different photo URL.
select * from User as a JOIN UserPhoto as up ON a.ID=up.UserId
Table User
FirstName | LastName | DOB | ID
ABC YZV 123 1234567
BCD XYZ 123 1234568
XYZ MCD 124 1234569
MNO XYZ 125 1234570
Table UserPhoto
ID | UserId | PhotoUrl
01 1234567 https://imageUrl1
02 1234567 https://imageUrl2
03 1234568 https://imageUrla1
04 1234568 https://imageUrlb1
Expected output:
{
fName: "ABC",
lName: "YZV",
dob: 123,
id: 1234567,
photos {
https://imageUrl1,
https://imageUrl2
}
}
3
You could group_concat the photos and then join on them:
SELECT u.*, photos
FROM users u
JOIN (SELECT userid, GROUP_CONCAT(photourl ORDER BY photourl ASC) AS photos
FROM userphoto
GROUP BY userid) up ON u.id = up.userid