CODEWITHSUNDEEP
mysql
user3564573
user3564573

Reputation: 700

Joining mysql tables twice to the same column

I have a table users with column profile_image and cover_image both of these point to id in TABLE images.

TABLE images (
  id,
  url,
  description,
  ...
)

TABLE users (
  profile_image (foreign key ON images.id),
  cover_image (foreign key ON images.id),
  ...
)

I want to retrieve:

  1. images.url AS profile_image after joining users.profile_image = images.id
  2. and images.url AS cover_image after joining users.cover_image = images.id.

Upvotes: 1

Views: 27

Answers (1)

esengineer
esengineer

Reputation: 9938

You could join twice as follows:

SELECT i.url AS profile_image, i2.url AS cover_image
FROM users u
INNER JOIN images i on u.profile_image = i.id
INNER JOIN images i2 on u.cover_image = i2.id
WHERE u.id = ....

I would also suggest naming foreign keys like profile_image_id and cover_image_id so that it is clear that the column points to some kind of an ID in a foreign table. Try to follow this convention.

Upvotes: 4

Related Questions