LEFT JOIN with multiple tables 1

[ctyankeeinok]

I have a query that involves multiple LEFT JOINs. I was using the Oracle (+) notation, but now the query has to be modified to include an OR condition, which is not supported by the (+) notation. So, here is the sample(the actual query is too big to wade through here):

Select a, b, c....
FROM Table1 t1, Table2 t2, Table3 t3, Table4 t4, Table5 t5, Table6 t6, Table7 t7
WHERE t1.field1 = t2.field1
AND t1.field2 = t6.field2
AND t6.field3 = t7.field3
AND t1.field4 = t3.field4 (+)
AND t1.field5 = t4.field5 (+)
AND t1.field6 = t4.field6 (+)
AND t2.field7 = t5.field7 (+)
AND 'IN PROCESS' = t5.field8 (+)

The problem is that t5.field8 must contain either 'IN PROCESS' OR 'READY TO DO'. I can't add an OR condition or IN statement (by ORACLE syntax rule). I have been poring over the ORACLE reference documents and I cannot figure out the LEFT JOIN syntax properly in the FROM clause when multiple B tables are LEFT JOINED to one A table.

 

[Dagon]

I usually get round this type of problem using an inline view e.g.

Select a, b, c....
FROM Table1 t1, Table2 t2, Table3 t3, Table4 t4, 
(select * from Table5 where field8 in ('IN PROCESS', 'READ TO DO') t5, 
Table6 t6, Table7 t7
WHERE t1.field1 = t2.field1
AND   t1.field2 = t6.field2
AND   t6.field3 = t7.field3
AND   t1.field4 = t3.field4 (+)
AND   t1.field5 = t4.field5 (+)
AND   t1.field6 = t4.field6 (+)
AND   t2.field7 = t5.field7 (+)

 

[Dagon]

Missed a bracket ther:


(select * from Table5 where field8 in ('IN PROCESS', 'READ TO DO')) t5

 

[ctyankeeinok]

Thanks! Worked very well.