MySQL - Join two rows to two columns

Question

I have two tables: user and photo. They look like this:

I need to perform a SELECT on the user table based on uuid, returning the url for both profile_photo and background_photo, if they exist.

These are essentially the final fields I need (the last two being JOINed from photo):

user.name, user.profile_photo_url, user.background_photo_url

WHERE user.uuid = SOME_UUID

Can somebody point me in the right direction with this statement?

Could you show us some data and what you need from it

Sashi Kant
– Sashi Kant

2016-03-15 05:39:15 +00:00
Commented Mar 15, 2016 at 5:39 — Sashi Kant
– Sashi Kant, Commented Mar 15, 2016 at 5:39

Rahul Patel · Accepted Answer · 2016-03-15 05:59:06Z

1

SELECT user.name, photo_a.url AS profile_photo_url, photo_b.url as background_photo_url FROM user LEFT JOIN photo as photo_a ON user.profile_photo_uuid = photo_a.uuid LEFT JOIN photo as photo_b ON user.background_photo_uuid = photo_b.uuid WHERE user.uuid = SOME_ID

edited Mar 15, 2016 at 5:59

answered Mar 15, 2016 at 5:44

Rahul Patel

6396 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hundley Over a year ago

This also works great, after altering background_photo_uuid to background_photo_url. Thanks!

Rahul Patel Over a year ago

Thanks. My mistake. Corrected my answer.

Hundley Over a year ago

This is similar to Dylan Su's answer, but I'm curious which is "better" from a performance perspective. Any ideas?

Rahul Patel Over a year ago

Both answers are same except, for Dylan Su's answer, it will not return any user which has not either of the photo columns' entry in "photo" table. In my case, it will return each user with NULL values for photo URLs field, if their entries do not exist in "photo" table.

Taj Ahmed · Accepted Answer · 2016-03-15 05:42:03Z

0

This should work for you

SELECT u.name,u.profile_photo_uuid,u.background_photo_url FROM user u, photo p 
WHERE u.uuid = **userid** AND 
( p.uuid = u.profile_photo_uuid  OR p.uuid = u.background_photo_url);

answered Mar 15, 2016 at 5:42

Taj Ahmed

89511 silver badges19 bronze badges

Collectives™ on Stack Overflow

MySQL - Join two rows to two columns

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related