Joining two tables & group by name

August 14, 2024

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
  }
}

>Solution :

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