开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜