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







All Articles