Q:

SQL query - link to lookup with two fields at once

I'd like to put together a query that links two fields from the THERAPY table to my lookup table REF_CODE; field PROT (which can be null) and field THER_TYPE (which is non-null).  REF_CODE contains multiple look up categories, distinguished by CAT_ID.  My query at the moment looks like this:
SELECT THER_ID, r1.CODE_NAME, r2.CODE_NAME
FROM THER t
LEFT JOIN REF_CODE r1 ON t.PROT = r1.CODE
JOIN REF_CODE r2 ON t.THER_TYPE = r2.CODE
WHERE EVENT_ID = 1234
AND r1.CAT_ID = '1'
AND r2.CAT_ID = '2';

There are two THERAPY records where EVENT_ID = 1234.  This query only returns the records where PROT is not null.  The record where PROT = null isn't returned at all. 
Why is this?  How can I return all the values, regardless of whether PROT is null or not? 

A:

Try this..
SELECT THER_ID, r1.CODE_NAME, r2.CODE_NAME
FROM THER t
LEFT JOIN REF_CODE r1 ON t.PROT = r1.CODE
and r1.CAT_ID = '1'
LEFT JOIN REF_CODE r2 ON t.THER_TYPE = r2.CODE
AND r2.CAT_ID = '2'
WHERE EVENT_ID = 1234;

Following are the change I made to your code:

You are doing an INNER JOIN in your 2nd JOIN with REF_CODE hence the NULL values would drop off the result.  I changed it to a LEFT JOIN
Your second issue boils down to the placement of your filter criteria in ON and WHERE clauses.  

Explanation of the 2nd point:
In an INNER JOIN, you could use the ON and WHERE interchangebly.  The underlying logic doesn't change and it also doesn't have an impact of the execution plan as well. 
Its quite different for the OUTER JOINs.  As you can see in your case a misplaced filter can skew the logic completely. 
a. Filter Criteria in the WHERE Clause i.e.
SELECT * FROM A left join B
ON A.id=B.id
WHERE B.id=1

The above query will function like an INNER JOIN and limit the entire result only to the row with B.id=1 (Note that the filter is applied to the table on the right of the LEFT JOIN operator)
b. Filter Criteria in the ON Clause i.e.
SELECT * FROM A left join B
ON A.id=B.id
AND B.id=1

The above query will only limit the rows of table B as they are being mapped to table A in the LEFT JOIN operator.  So the result will contain all rows of table A and values in the columns for table B only for the rows that match the condition B.id=1

