CODEWITHSUNDEEP
mysqlsqljoinleft-joinwhere-clause
user1257255
user1257255

Reputation: 1171

MySQL: Get results only if there is specific user id or user id is null

We have two tables:

We need to display competition id, two opponents, date from Competitions table and result, user id from Results table. We'll be searching by Results and if user has already submitted his result then we display his id and result. Else we just display null in those two fields but there must not be any result of any other user then this one which we provide. I came up with this query, but it's not showing all competitions if there is not provided some user's id which has already submitted it's result.

SELECT c.competition_id, c1.name AS first, c2.name AS second, c.date, r.result_id, r.user_id 
FROM competitions c
JOIN countries c1 ON c.first=c1.country_id
JOIN countries c2 ON c.second=c2.country_id
LEFT JOIN results r ON c.competition_id=r.competition_id
WHERE c.date='2013-07-04' AND (r.user_id=1 OR r.user_id IS NULL)

What do I have to do to solve my problem?

Upvotes: 0

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I think you need to move the r.user_id comparison into the on clause:

SELECT c.competition_id, c1.name AS first, c2.name AS second, c.date, r.result_id, r.user_id 
FROM competitions c
JOIN countries c1 ON c.first=c1.country_id
JOIN countries c2 ON c.second=c2.country_id
LEFT JOIN results r ON c.competition_id=r.competition_id and r.user_id = 1
WHERE c.date='2013-07-04'

This will keep all competitions, but return results only for the given user.

I believe your query is filtering out results where someone other than the user has submitted results. This is because there is a match on the row, so r.user_id is not NULL. However, the user id does not match the one you are looking for either.

Upvotes: 1

Related Questions