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

debugcn Published at Dev

BlackBishop

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?

Andomar

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-5

Comments

0 comments

From Dev

Mysql left join with condition in right table

From Dev

Mysql left join with condition in right table

From Dev

Mysql LEFT JOIN same table with WHERE condition

From Dev

Mysql LEFT JOIN same table with WHERE condition

From Dev

left join with where in right table

From Dev

LEFT OUTER JOIN selects just one row

From Dev

Perform right outer join with a condition for left table

From Dev

Mysql Left join with One row

From Dev

query for join two column and get one row per left with column that have array of right table refrenced to left table row

From Dev

mysql LEFT JOIN with condition from other table

From Dev

Where clause for only one table in a left join

From Dev

MySQL JOIN returning unrelated rows when combined with LEFT JOIN, WHERE and OR

From Dev

How do I do a LEFT JOIN in MySQL, where I have missing key values in the RIGHT table?

From Dev

What is the proper syntax for multiple left join and where condition on the left table?

From Dev

MySQL left join limit to one row

From Dev

MySQL left join limit to one row

From Dev

mysql left join and group by not returning all results in left table

From Dev

mysql left join - returning rows using NULL and a condition

From Dev

left join tables, use only one row from left table

From Dev

Laravel Left join add where clause to right table

From Dev

Laravel Left join add where clause to right table

From Dev

MySQL LEFT JOIN with OR condition

From Dev

mysql return one row of right table

From Dev

How to return only one row from the right-most table using mysql join

From Dev

MySql LEFT JOIN returns only one NULL row from the other table

From Dev

MySQL join tables where all left records are in right

From Dev

LEFT JOIN WHERE not returning results

From Dev