SQL where query removing unintended rows

user_14638621 0
Viewed 30

I have this SQL query:

SELECT
    e.Id, MAX(r.EventId), MAX(r.UserId), 
    e.Name, MAX(r.Description)
FROM Events e 
LEFT JOIN Results r ON e.Id = r.EventId
GROUP BY e.Id, e.Name;

It correctly outputs this result set:

enter image description here

However, when I add a where clause so it doesn't include userID 86, it removes most rows as you can see below in the image below. What I'm trying to do is so that it only removes row with 86 as the UserId.

enter image description here

This is the code with where clause:

SELECT
    e.Id, MAX(r.EventId), MAX(r.UserId), 
    e.Name, MAX(r.Description)
FROM 
    Events e 
LEFT JOIN 
    Results r ON e.Id = r.EventId
WHERE
    r.UserId != 86 
GROUP BY
    e.Id, e.Name;
sql
1 Answers

Place the extra predicate in the ON clause, as in:

Select e.Id, max(r.EventId), max(r.UserId), e.Name, max(r.Description)
FROM Events e LEFT JOIN Results r
ON e.Id = r.EventId AND r.UserId <> 86 
group by e.Id, e.Name;
user_64361910
Related