Mysql联接多个查询

debugcn 发表于 Dev

打

我有三张桌子。我正在尝试为此查询

我为此写了两个查询

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，title和第二个查询

is_deleted，标题

但是我想要结果类似is_fav，is_deleted，title

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

谢谢

丹尼尔·W。

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

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