Need help with Left Outer Join !!!

[Slingky]

3 tables

PartTran -> ShipDtl -> ShipHead

Between PartTran & ShipDtl = Left Outer Join
Between ShipDtl & ShipHead = Equal Join

All is ok.

Records from PartTran who don't have corresponding in ShipDtl are listed.

But if I put "ShipDate", a field from ShipHead, only the records that match between PartTran and ShipDtl are displayed.

Why it is not functioning ? Can't I put a table after a Left Outer Join ?

My Database is Progress 9.1b and I use Crystal Reports Professional 8.0

P.S. (I can't reach ShipHead by any other link, so I must pass by ShipDtl which is already a Many to One relationship)

Thanks in advance.
Maxime

 

[lyanch]

Yes you can add a table after a left outer join.. but the link there also has to be a left outer join (even if you don't need it)

Lisa

 

[Slingky]

If I do that, I get an error "column ambiguously specified" and it doesn't say which column ????

 

[OldTimeProgrammer]

Try two Left Joins:

SELECT whatever
FROM (PartTran LEFT JOIN ShipDtl ON PartTran.field1 = ShipDtl.field1)
LEFT JOIN ShipHead ON ShipDtl.field2 = ShipHead.field2

This works in Access. Gets all PartTran records whatever fields are selected.

 

[davesm]

Hi Slingky

Did you get an answer to this Left Outer problem, I have exactly the same problem, the join table is necessary to further link to the actual values but when I use a left outer to return all the records from the first table, regardless of partners i the second, the tables further to the right don't work with your same ambiguous errors.

The only way I can get this to go right through is INNER or EQUI join everything.

I have used code to return master groups, even if their child records are not existing but it's hardly elegant.

Cheers
DAVE SMITH