CODEWITHSUNDEEP
sqloracle11g
davemere
davemere

Reputation: 5

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?

Upvotes: 0

Views: 201

Answers (2)

ElRojo
ElRojo

Reputation: 548

I created a SQL Fiddle query to test your question. Please review to verify that it matches your scenario.

The where clause is trumping the left join. Move the two filters on Cat_ID into the join clauses like this:

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

The other less clean way to do this is to surround the part of the where clause related to the left join with isnull() like this:

SELECT THER_ID, r1.CODE_NAME, r2.CODE_NAME as Name2
FROM THER t
LEFT JOIN REF_CODE r1 ON t.PROT = r1.CODE
JOIN REF_CODE r2 ON t.THER_TYPE = r2.CODE AND r2.CAT_ID = '2'
WHERE EVENT_ID = 1234
AND Coalesce(RTRIM(r1.CAT_ID),'1') = '1'

See this similar answer for Left Outer Join Not Working.

Additional:

If @SoulTrain's query worked, then you must also have rows in Ther that do no match rows in Ref_Code. In that case you do need to use two left joins, but the order of the joins do not matter. Please see updated SQL Fiddle Example to see this query in action. It is the last query. 

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;

Upvotes: 1

SoulTrain
SoulTrain

Reputation: 1904

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:

  1. 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

  2. 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

Upvotes: 1

Related Questions