CODEWITHSUNDEEP
sqlleft-join
Veljko
Veljko

Reputation: 1798

SQL Joining three tables and using LEFT OUTER JOIN

I have three tables and two seperate SQL queries which are working correctly and I am having correct results. If I try to join these three tables I am having null as result.

First query:

select T1.ID,T3.COMPANY
from T1,T3
where (T1.status!='CLOSED') and (T1.PRIORITY)>5 and T1.CLASSID=T3.CLASSID

Second query:

SELECT T1.ID, T2.DESCRIPTION 
FROM T1
LEFT OUTER JOIN T2
ON T1.ID=T2.KEY 
WHERE    T1.status!='CLOSED' 
AND (T2.CREATEDATE= (SELECT MAX(CREATEDATE) 
FROM T2
WHERE  T2.KEY=T1.ID))

I tried to join them but as result I am having null:

select T1.ID,T3.COMPANY,T2.DESCRIPTION 
from T1
INNER JOIN T3 ON T1.CLASSID=T3.CLASSID
LEFT OUTER JOIN  T2
ON T1.ID=T2.KEY 
 where (T1.status!='CLOSED') AND  (T1.PRIORITY)>5
AND (T2.CREATEDATE= (SELECT MAX(CREATEDATE) 
FROM T2
WHERE T2.KEY=T1.ID))

like it does not recognized last part for taking MAX value from T2 table.

What am I doing wrong? Thanks for help

Upvotes: 0

Views: 3006

Answers (2)

Anthony Faull
Anthony Faull

Reputation: 17957

Firstly, use an alias for the subquery on table T2. 

T2.CREATEDATE =
        (SELECT MAX(T2Alias.CREATEDATE)
        FROM T2 AS T2Alias
        WHERE T2Alias.KEY = T1.ID)

Secondly, consider moving this condition into the ON clause of the LEFT JOIN to table T2.

Upvotes: 2

Ryan Kenning
Ryan Kenning

Reputation: 99

The first thing that jumps out at me is the new dependency on both T1.Priority > 5 and T2.CreateDate value being equal to the result of the inline query:

( AND (T1.PRIORITY) > 5
  AND (T2.CREATEDATE =
  (SELECT MAX(CREATEDATE) FROM T2 WHERE T2.KEY = T1.ID) )

Without the data it's difficult to check however this may be the issue

Upvotes: 0

Related Questions