Search code examples
mysqlleft-joincoalesce

SQL select default value when there is no such value

I have following tables in DB.

ACCOUNT TABLE

  User_id|  first_name |  last_name |   age   |        
  _______|_____________|____________|_________|
     1   |    LeBron   |   James    |    28   |
     2   |    Kobe     |   Bryent   |    29   |
     3   |    Kevin    |   Durant   |    30   |
     4   |    Jim      |   Jones    |    31   |
     5   |    Paul     |   Pierce   |    32   |
     6   |    Jeremy   |   Lin      |    33   |

USER_BOOKMARK TABLE

  User_id|  Bookmarked_user_id
  _______|____________________
     1   |     2        
     1   |     3      
     1   |     4
     2   |     1        
     2   |     4     
     3   |     1
     5   |     6

I want to select user's information from ACCOUNT table and also whether that person is in my Bookmark list

ex) Lebron James wants to know Jeremy Lin's information and whether Jeremy is in he's bookmark lists.

Desired results => 

  User_id|  first_name |  last_name |   age   | isBookmarked |       
  _______|_____________|____________|_________|______________|      
     6   |    Jeremy   |     Lin    |    33   |      0       |  =>0 means no.

  *It must return only one row.
  *If user is on my bookmark list, value of isBookmarked is my user_id.

What I tried =>

 SELECT ACCOUNT.user_id, ACCOUNT.firstname, ACCOUNT.lastname, coalesce(User_Bookmark.user_id, 0) as isBookmarked
 FROM Account LEFT OUTER JOIN User_Bookmark ON Account.user_id = User_Bookmark.Bookmarked_user_id
 WHERE Account.user_id=6 AND User_Bookmark.user_id=1

But this query returns zero rows... since I'm not an expert on sql, I assume that I'm missing something. Can anyone help me?

Solution

  • The User_Bookmark.user_id = 1 test is filtering out the non-matching rows, because that column will be NULL when there's no match. When doing a LEFT JOIN, you have to put conditions on the second table into the ON clause rather than WHEN.

    SELECT ACCOUNT.user_id, ACCOUNT.firstname, ACCOUNT.lastname, coalesce(User_Bookmark.user_id, 0) as isBookmarked
FROM Account 
LEFT OUTER JOIN User_Bookmark 
    ON Account.user_id = User_Bookmark.Bookmarked_user_id AND User_Bookmark.user_id=1
 WHERE Account.user_id=6