Mysql连接多个查询

Question

我有三张桌子。我正试着写一个查询

我已经写了两封问询

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

和下面的第二个查询

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

这些查询运行得很准确。

现在，我的第一个查询给出了is_fav、标题和第二个查询。

is_deleted，标题

但我想要的结果像is_fav，is_deleted，标题

任何人都告诉我如何达到这个结果。我想在mysql中实现这个结果。

谢谢

Answer 1

将这两个查询合并为一个查询。

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