Multiple left joins

[priyanthan]

I have 3 tables, tableA tableB & tableC
tableA left join tableB and tableA left join tableC

My question is, is this kind of joins (multiple left joins) supported in crystal 8.5. (Database DB2)

Thanks,

 

[lbass]

There may be database-specific exceptions, and I'm not sure about DB2, but in general, yes, you should be able to do this. Why do you ask?

-LB

 

[IanWaterman]

Just remember if you put a filter condition on table B or C that will over ride the Left outer join.

eg you can not just add a filter like

TableB.Field = 'X'

you should use
(Isnull(TableB.Field) or TableB.Field = 'X')

Ian

 

[lbass]

Actually, you can't put any selection on outer tables if there is a possibility that, in Ian's example, TableB.Field could equal 'Y'--any TableA records where this was true, would not appear. If a field can ONLY be null or equal to 'X', then Ian's suggestion would work.

There is a way you can handle this in a command though, by building the selection criteria in outer tables into the From clause of the query.

-LB

 

[IdoMillet]

An alternative to using a command is to create a VIEW that applies the filter criterion. Then, Outer Join to the View.

Or you can create the View to include the joins and take care of things by including the filter condition in the ON clause rather than the WHERE clause.

hth,
- Ido


view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com