LEFT outer join - pains

[former_member681242]

Hello,

Can some one tell me how to do a left outer join in Crystal for two (2) different scenarios:

(1.) Two tables and two fields in both tables

(2.) Three tables one field from table A, one field from table B, and two fields from table C. The Left is table A and B.

I knowhow to do a Left outer join with two tables and one field.

The reason for the Left outer join is, I want the Left portion of data to be present even if there is not a match.

Thanks,

Paul

[former_member681242]

Hi Paul,

FOR (1) ...

Select TABLE1.* ,TABLE2.*

FROM TABLE1

LEFT OUTER JOIN TABLE2 ON TABLE2.FIELD1 = TABLE1.FIELD1

AND TABLE2.FIELD2 = TABLE1.FIELD2

FOR (2) ...

Select TABLE1.* ,TABLE2.*, TABLE3.*

FROM TABLE1

LEFT OUTER JOIN TABLE2 ON TABLE2.FIELD1 = TABLE1.FIELD1

LEFT OUTER JOIN TABLE3 ON TABLE3.FIELD1 = TABLE1.FIELD1

AND TABLE3.FIELD2 = TABLE1.FIELD2

Kind regards

Tobias

[DellSC · Accepted solution]

You would Left Outer from A to C and Right Outer from C to B so that the links are "chained". Unfortunately, since there is no way to link from A to B, if there is no data in C, you'll only get data from A and B will be blank.

The query you show would look like this:

Select * from A
Left join C as u
ON A.first_name = u.first_name 
  and A.Las_name = u.Last_ name

You don't need to use "Select * from..." for the table you're joining to - just join directly to the table.

-Dell