Joining of three tables in MySQL
I have 2 tables (photos, photos_details) joined by a LEFT JOIN and I w开发者_Python百科ant to JOIN a third one (favourites) to see if the photo is favourited or not. This is the current SQL query:
SELECT photos_details.title, photos_details.description, photos.url
FROM photos
LEFT JOIN photos_details ON photos.photo_id = photos_details.photo_id
favourites table basically contains 2 columns (PRIMARY) id and photo_id
Do you know how I could do that?
LEFT JOIN
against the favorites table and determine with a CASE
whether the favorites.photo_id
is null, transforming it into a boolean TRUE
or FALSE
if the photo has a favorite.
SELECT
photos_details.title,
photos_details.description,
photos.url,
CASE WHEN favorites.photo_id IS NOT NULL THEN TRUE ELSE FALSE END as is_favorited
FROM photos
LEFT JOIN photos_details ON photos.photo_id = photos_details.photo_id
LEFT JOIN favorites ON photos.photo_id = favorites.photo_id
To get only favorited photos, add
WHERE favorites.photo_id IS NOT NULL
精彩评论