Left Join

[Triton46]

I am having an issue with a left join where I will not get any results back, however, if I change the columns I am selecting I do get results back.

select a, b
from table_1, table_2, table_3
where (table_3(+) = table_2
and table_2 = table_1)
and (a = 'a'
and b = 'b'
and c = 'c')

no rows selected

select a, b, c
from table_1, table_2, table_3
where (table_3(+) = table_2
and table_2 = table_1)
and (a = 'a'
and b = 'b'
and c = 'c')

a b c
- - -
a b c
a b c

Does anyone understand this? Even if I do a count, I get 0 rows.

 

[JamesGordon]

What does the tables look like. What tables do a, b & c belong to.
table_3(+) = table_2??? What columns are you linking on?
Can you give a proper example with code you know fails. If you cannot show us the real code then create a test case and code that is syntatically correct.

 

[Triton46]

OK, here is the exact code:

SELECT al2.target_name, al4.location_cd
FROM DDW_RTP.KINASE_PIC50 AL1,
DDW_DBA.TARGET AL2,
DDW_DBA.SCREEN AL4,
DDW_DBA.SCREEN_PROPERTIES AL5,
DDW_DBA.COMPOUND AL7,
DDW_RTP.COMPOUND_CALC_PROPERTIES AL11
WHERE ( AL7.MOLNAME = AL11.MOLNAME(+)
AND AL2.TARGET_KEY=AL1.TARGET_KEY
AND AL4.SCREEN_KEY=AL1.SCREEN_KEY
AND AL5.TECHNOLOGY_KEY=AL1.TECHNOLOGY_KEY
AND AL7.COMPOUND_LOT_KEY=AL1.COMPOUND_LOT_KEY)
AND (AL2.TARGET_NAME = 'ALK5'
AND AL4.LOCATION_CD = 'HAR'
AND AL5.ASSAY_FORMAT = 'FP'
AND AL5.ASSAY_SUBTYPE = 'Binding')
/

no rows selected

1 SELECT al2.target_name, al4.location_cd, al5.assay_subtype
2 FROM DDW_RTP.KINASE_PIC50 AL1,
3 DDW_DBA.TARGET AL2,
4 DDW_DBA.SCREEN AL4,
5 DDW_DBA.SCREEN_PROPERTIES AL5,
6 DDW_DBA.COMPOUND AL7,
7 DDW_RTP.COMPOUND_CALC_PROPERTIES AL11
8 WHERE ( AL7.MOLNAME = AL11.MOLNAME(+)
9 AND AL2.TARGET_KEY=AL1.TARGET_KEY
10 AND AL4.SCREEN_KEY=AL1.SCREEN_KEY
11 AND AL5.TECHNOLOGY_KEY=AL1.TECHNOLOGY_KEY
12 AND AL7.COMPOUND_LOT_KEY=AL1.COMPOUND_LOT_KEY)
13 AND (AL2.TARGET_NAME = 'ALK5'
14 AND AL4.LOCATION_CD = 'HAR'
15 AND AL5.ASSAY_FORMAT = 'FP'
16* AND AL5.ASSAY_SUBTYPE = 'Binding')
SQL> /

TARGET_NAME LOCATION_C ASSAY_SUBTYPE
------------------------- ---------- --------------------
ALK5 HAR Binding
ALK5 HAR Binding
ALK5 HAR Binding

I have found that you must select a column from at least three of the lower where criteria (not the joins). If you don't, you get no data.