Finding rows which match and blanks

Question

I have a table of users and a table of electronic signatures - I want to be able to show for a given document who has signed and who has not.

Lets say we have employees:

EID   Name
1     Bob
2     Tom
3     Fred

signatures:

EID    Document Signature
1      1        Bob
1      2        Bob
1      3        Bob
2      1        Tom
3      2        Fred

My issue is that I can get this to work fine for document 4 - as no one has signed I can look where the document is null

However, if I look for document 2, for example, then I am currently getting employees missed off the list

For document 2 I would want to see

EID Signature
1   Bob
2 
3   Fred

For document 4 I would want to see:

EID Signature
1
2 
3

and for document 1:

EID Signature
1   Bob
2   Tom
3

The query I have tried to use is:

SELECT e.eid, s.signature 
from employees e 
left join signatures s on e.eid=s.eid 
where s.document=? or s.document IS NULL group by e.eid

Answer 1

There are multiple issues:

• Whenever using Left Join, any Where conditions on the right-side tables, should be put in the On clause. Otherwise, it will filter your result-set, even if there is no matching row (losing the purpose of Left Join)/
• To compare null values, we use IS NULL. = null does not work. In this case, if we shift the conditions to On clause, we don't need to check for null values either.
• Group By usage is invalid and really not required. When using Group By, only aggregated columns or the columns specified in Group By should come in Select. Refer: https://stackoverflow.com/a/41887524/2469308

Try the following:

SELECT e.eid, s.signature 
FROM employees e 
LEFT JOIN signatures s 
  ON e.eid=s.eid AND 
     s.document = ?