Left outer join not working

Question

Hello all I have below tables -

tr_resource as res -

Resourceid  name
135021          ABC

tr_charac as chlev -

characid   name
1          CH1
2          CH2

tr_characvalue as cvlev -

characvalueid characid stringvalue
cv1             1       XX
cv2             2       YY
cv3             2       zz

tr_resourcecharac as rchlev -

resourceid  characvalueid
135021          cv2

When i run below query -

select 
    res.name,
    cvlev.stringvalue
from tr_resource res
    left outer join tr_resourcecharac rchlev on res.RESOURCEID = rchlev.RESOURCEID
    inner join tr_characvalue cvlev on  rchlev.CHARACVALUEID = cvlev.CHARACVALUEID 
    inner join tr_charac chlev on chlev.CHARACID = cvlev.CHARACID and chlev.NAME = 'CH2'
 where res.resourceid=135021

I am getting answer as -

name  stringvalue
ABC      YY

but when i run below query, i am not getting any answer -

select 
    res.name,
     cvlev.stringvalue
 from tr_resource res
     left outer join tr_resourcecharac rchlev on res.RESOURCEID = rchlev.RESOURCEID
     inner join tr_characvalue cvlev on  rchlev.CHARACVALUEID = cvlev.CHARACVALUEID 
     inner join tr_charac chlev on chlev.CHARACID = cvlev.CHARACID and chlev.NAME = 'CH1'
 where res.resourceid=135021

I want to get answer as -

name  stringvalue
ABC      null

Could you please help?

Answer 1

This doesn't explain why your first query is working, but according to the data that you have shown I don't think that the join:

chlev.CHARACID = cvlev.CHARACID

should work and chlev is an alias for tr_charac and the CHARACID's in the table are 1 and 2 whereas cvlev is an alias for tr_characvalue and the CHARACID's here are ch1, ch2 & ch2.

UPDATE

Thanks for the clarification. I think that the issue is with the line below:

inner join tr_characvalue cvlev on  rchlev.CHARACVALUEID = cvlev.CHARACVALUEID

You are trying to join these two tables on CHARACVALUEID, but the only CHARACVALUEID in tr_resourcecharac is CV2, hence this will only find the CV2 row in tr_characvalue. tr_characvalue joins to tr_charac on characid and will therefore only find the CH2 row. As your query then filters to chlev.NAME = 'CH1'no results are returned.

The point is that with the data that you have you cannot join from tr_resource to tr_characvalue (even using the other tables with outer joins etc) as the only link between them is the data in tr_resourcecharac which currently only has a record for CV2.

Answer 2

You have to change all the inner joins to left outer joins.