MySQL query LEFT JOIN and WHERE clause

Question

I have 2 tables i need to join

events
------
eventID | date
   1      2019
   2      2019
   3      2020

results
-------
 eventID | playerID | totalScore
    1          1          34
    2          2          35
    3          1          36

I want to get a result for a selected player ID as below so it show all events and the total scores for each event that the player has played in. e.g for Player 1


eventID | totalScore
   1          34
   2         NULL
   3          36

I'm assuming i need a left join, I have tried numerous queries e.g

SELECT events.eventID, results.totalScore, results.playerID
FROM events
LEFT OUTER JOIN results
ON events.eventID = results.eventID WHERE results.playerID=1

Most of my queries only show the actual events where selected player has a score.

I'm thinking it may be to do with the where clause but I'm unsure how to proceed.

A duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jan 18 '20 at 23:43
This is a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Jan 18 '20 at 23:43
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. — philipxy, Jan 18 '20 at 23:43
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After an OUTER JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Jan 18 '20 at 23:45

GMB · Accepted Answer · 2020-01-18T21:25:33.517

3

You need to move the condition on the left joined table from the where clause to the on part of the join. Otherwise, the condition becomes mandatory, and eliminates unmatched records from the resultset.

SELECT e.eventID, r.totalScore, r.playerID
FROM events e
LEFT OUTER JOIN results r ON e.eventID = r.eventID AND r.playerID=1

edited Jan 18 '20 at 21:25

answered Jan 18 '20 at 20:54

GMB

216,147
25
84
135

Thanks for this should all columns in a row with no totalScore show as NULL, i was expecting the eventID and playerID columns to be populated with corresponding values and just totalScore to show as NULL? – Jon Jan 18 '20 at 21:24
@Jon: my bad, I just fixed the query so `eventID` is displayed. For `playerID`, that's a fixed value anyway... – GMB Jan 18 '20 at 21:26
1

of course playerID is fixed...sorry! Thanks for assisting, much appreciated! – Jon Jan 18 '20 at 21:29

MySQL query LEFT JOIN and WHERE clause

1 Answers1