Nested LEFT and INNER Joins

[cbochner]

Hi,

I have a join sequence as follows:

from
stoordre a
LEFT JOIN bhrcustc b
ON a.cust_code = b.cust_code
LEFT JOIN bhroapsd child
ON a.cust_code = child.cust_code
INNER JOIN bhroapse parent
ON child.p_code = parent.p_code
INNER JOIN strcustr cust
ON parent.cust_code = cust.cust_code,
stxgropd grp,
bhotimes tme

I would like the first and second INNER join to be nested (grouped) amongst themselves. As if to say, give me all records regardless of bhrcustc, in turn give me all the records regardless bhroapsd. However, bhroapsd and bhroapse and strcustr, I would like to be joined together as INNER. How would I correctly use brackets, etc..

Can anyone please help?

Thank You,

Chaim Bochner

 

[IanWaterman]

You can not have Inner joins hanging off Left joins and expect them to return all data. YOu will have to restructure query along these lines.

stoordre a
INNER JOIN strcustr cust
ON a.cust_code = cust.cust_code
INNER JOIN bhroapse parent
ON cust.cust_code = parent.cust_code
LEFT JOIN bhrcustc b
ON parent.cust_code = b.cust_code
LEFT JOIN bhroapsd child
ON b.cust_code = child.cust_code
stxgropd grp,
bhotimes tme
where (child.p_code is null or child.p_code = parent.p_code )

Ian