How to LEFT JOIN two simple tables but not throwing away data from the second table?

Asked Apr 07 '20 at 12:56

Active Apr 07 '20 at 13:00

Viewed 17 times

My SQL: http://sqlfiddle.com/#!9/350efb/3

I am trying to LEFT JOIN two tables, and filter their values in my query. However, there are rows in the second table that have no matching in first table. I would like to return NULL if there are missing rows.

Expected result is:

oid bid name              oid    uid  value
1   1   Book 1  Name 1    1      1    User 1 OID 1
2   1   Book 1  Name 2    NULL   NULL NULL
3   1   Book 1  Name 3    NULL   NULL NULL
4   1   Book 1  Name 4    NULL   NULL NULL
5   1   Book 1  Name 5    5      1    User 1 OID 5

However, in my actual results as seen in the link, I only got two rows. All my rows with a NULL were not returned.

Q: How to achieve my expected output? I tried to add OpeningUser.uid is NULL but it didn't work.

My current query is:

select * from Opening
LEFT JOIN OpeningUser ON Opening.oid = OpeningUser.oid
WHERE Opening.bid=1 AND (OpeningUser.uid=1 OR OpeningUser.uid is NULL)
ORDER BY Opening.oid

edited Apr 07 '20 at 13:00

asked Apr 07 '20 at 12:56

ABCD

7,914
9
54
90

Show us your current query attempt. – jarlh Apr 07 '20 at 12:56
1

I'd guess you're having right side table conditions in the WHERE clause? Move such to the ON clause. – jarlh Apr 07 '20 at 12:57
@jarlh It's in the link. – ABCD Apr 07 '20 at 12:57
I made 100% reproducible example. – ABCD Apr 07 '20 at 12:58
http://sqlfiddle.com/#!9/350efb/6 – Nick Apr 07 '20 at 12:59
Great, but remember that SO is an archive, and the sqlfiddle will be gone a long time before SO. – jarlh Apr 07 '20 at 12:59
@jarlh you were right anyway. – Nick Apr 07 '20 at 13:00
Just in case you hadn't realised, the link I posted in my earlier comment is a working solution to your question. – Nick Apr 07 '20 at 13:17

How to LEFT JOIN two simple tables but not throwing away data from the second table?

0 Answers0