Mysql Join multiple queries
I have three tables. I am trying to write a query for this
I wrote two requests for this
1)
SELECT
`user_favourite_bookmarks`.`user_bookmark_id` as `is_fav`,
`user_bookmarks`.* FROM `user_favourite_bookmarks`
RIGHT JOIN
`user_bookmarks` ON `user_favourite_bookmarks`.`user_bookmark_id` = `user_bookmarks`.`bookmark_id`
WHERE
`user_bookmarks`.`user_id`=26
group by `user_bookmarks`.`bookmark_id`
order by created_at DESC
and second query below
2)
SELECT `user_deleted_bookmarks`.`bookmark_id` as `is_deleted`,`user_bookmarks`.*, FROM `user_deleted_bookmarks`
RIGHT JOIN
**`user_bookmarks` ON `user_deleted_bookmarks`.`bookmark_id` = `user_bookmarks`.`bookmark_id`
WHERE
`user_bookmarks`.`user_id`=26
group by `user_bookmarks`.`bookmark_id`
order by created_at DESC
Accurate works in these queries
now my first request gives me, like is_fav, the header and also the second
is_deleted, name
But I want to get something like is_fav, is_deleted, title
Someone tell me how to achieve this result. I want to achieve this result in mysql.
thank
+3
source to share
1 answer
Combine these two queries into one query.
SELECT
b.title,
IF(f.bookmark_id IS NULL, 'no', 'yes') AS 'favorite',
IF(d.bookmark_id IS NULL, 'no', 'yes') AS 'deleted'
FROM user_bookmarks b
LEFT JOIN user_deleted_bookmarks d
ON d.bookmark_id = b.bookmark_id
LEFT JOIN user_favourite_bookmark f
ON f.bookmark_id = b.bookmark_id
WHERE b.user_id = 26
GROUP BY b.bookmark_id
ORDER BY b.created_at DESC
+2
source to share