Mysql Join multiple Query

Question

I have three tables. I am trying to write a query for that

I have written two Queries for that

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

These Queries are running Accurate

now my first query gives me out like is_fav, title and second one

is_deleted, title

But i want result something like that is_fav, is_deleted, title

Anybody tell me how to achieve this result. I want to achieve this result in mysql .

Thanks

Daniel W. · Accepted Answer · 2014-08-12 09:01:18Z

2

Merge those 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

answered Aug 12, 2014 at 9:01

Daniel W.

32.6k15 gold badges99 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hitu Over a year ago

there is not such field like is_fav, Please read my query carefully

Daniel W. Over a year ago

@Hitu Sorry Sir, I updated the answer in respect to your avail columns!

Hitu Over a year ago

Can you tell me how to get records if deleted is no only from above statement

Collectives™ on Stack Overflow

Mysql Join multiple Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related