CODEWITHSUNDEEP
mysql
Frans Eric Dela Cruz
Frans Eric Dela Cruz

Reputation: 115

mysql joining table with null

How can I join those two tables?

Breaks table

+----+-------------+
| id | description |
+----+-------------+
|  1 | Sample      |
|  2 | Sample 2    |
+----+-------------+

User favorites

+----+---------+-----------+
| id | user_id | breaks_id |
+----+---------+-----------+
|  1 |       2 |         1 |
+----+---------+-----------+

My expected Output

+-----------+----------+--------------+-------------+
| breaks_id | user_id  | is_favorite  | description |
+-----------+----------+--------------+-------------+
|         1 | 2        | 1            | Sample      |
|         2 | null     | null         | Sample      |
+-----------+----------+--------------+-------------+

I want to view all list of breaks with user_id =1 then null if not favorites

Upvotes: 0

Views: 40

Answers (2)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

it will work 

SELECT br.id AS break_id, fv.user_id, fv.id AS is_favorite,
 br.description
 from breaks br
 left join  favorites fv
 on br.id = fv.id
 where fv.id is null;

Upvotes: 1

Stidgeon
Stidgeon

Reputation: 2723

I think you just want to use an OUTER JOIN here:

SELECT b.id AS break_id, f.user_id, f.id AS is_favorite, b.description
FROM breaks b
LEFT JOIN 
user_favorites f
ON b.id = f.breaks_id;

The LEFT JOIN is an outer join, that will SELECT all the records from the breaks table, and join them with corresponding entries in the user_favorites table. If there are no matches, the relevant fields will be Null.

Upvotes: 1

Related Questions