CODEWITHSUNDEEP
mysqlsql
BlackBishop
BlackBishop

Reputation: 737

MySQL LEFT JOIN is returning just one row with a where condition on the right table

I have two tables, one with events and a second one with events marked as saved by a user.

I need to get all the events, and for a certain user to know whether an event is saved or not.

For doing that I am using this SQL

SELECT event.id, saved.user_id
FROM event
LEFT JOIN saved on event.id = saved.event_id

but then I am getting repeated events, as one event may be marked as saved by many users at the saved table.

Therefore I've added a where condition as

SELECT event.id, saved.user_id
FROM event
LEFT JOIN saved on event.id = saved.event_id
where saved.user_id = "71"

but surprisingly, the result obtained is reduced to just one row, when I was expecting thousands of them, most of them with saved.user_id to null and only a few or just once with saved.user_id = 71

Ideally I would like to have true/false at the saved.user_id column for a certain user, eg: 71

What is wrong here?

Upvotes: 0

Views: 46

Answers (1)

Andomar
Andomar

Reputation: 238076

Change:

LEFT JOIN saved on event.id = saved.event_id
where saved.user_id = "71"

To:

LEFT JOIN saved on event.id = saved.event_id
                   and saved.user_id = "71"

Or:

LEFT JOIN saved on event.id = saved.event_id
WHERE saved.user_id = "71" OR saved.user_id IS NULL

The where clause filters data from all tables, the on clause just filters the rows from the right-hand table.

Upvotes: 1

Related Questions