Using JOIN to include NULL values

Question

My goal to combine results from two tables (predictions and user_score) so that I have all users who scored the points in the match and who did not score the points, but are participating in the pool (poolid=110)

predictions

predid    | matchid  | ueserid    | points |
--------------------------------------------
1            121            8        1
2            122            8        2
3            122            10       3

user_score

id    | poolid  | userid    | 
------------------------------
1         110            8
2         110            10
3         111            8
4         111            10

Results should be like that:

| matchid  | ueserid    | points |  poolid 
--------------------------------------------
  121            8          1      110
  null           10         null   110

This is few queries that i tried:

SELECT * FROM predictions LEFT JOIN user_score on predictions.userid = user_score.userid WHERE predictions.matchid=121 and user_score.poolid=110

SELECT * FROM predictions RIGHT JOIN user_score on predictions.userid = user_score.userid WHERE predictions.matchid=121 and user_score.poolid=110

This query doesnt work on my SQL version Apache/2.4.26 (Win32) OpenSSL/1.0.2l PHP/5.6.31 / Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503 /

SELECT * FROM predictions FULL OUTER JOIN user_score on predictions.userid = user_score.userid WHERE predictions.matchid=121 and user_score.poolid=110

I need help :)

MySQL does not support FULL OUTER JOIN

juergen d
– juergen d

2018-01-14 18:30:55 +00:00
Commented Jan 14, 2018 at 18:30 — juergen d
– juergen d, Commented Jan 14, 2018 at 18:30

Paul Spiegel · Accepted Answer · 2018-01-14 18:31:45Z

2

Use a LEFT JOIN, and put the condition matchid = 121 into the ON clause:

select p.matchid, s.userid, p.points, s.poolid 
from user_score s
left join predictions p
  on  p.userid  = s.userid
  and p.matchid = 121
where s.poolid = 110

Demo: http://sqlfiddle.com/#!9/2a1a5/1

You can also use your RIGHT JOIN method, but the matchid = 121 condition must be in the ON clause.

http://sqlfiddle.com/#!9/2a1a5/6

answered Jan 14, 2018 at 18:31

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using JOIN to include NULL values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related