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

[DellSC]

You can't join from one field to two fields - it won't work. What do the two different fields represent?

-Dell

[former_member731159]

Sorry about that, I wasn’t very clear.

If I have table A with a field called Name,

Table B with a field named SSN

And Table C with two fields - one named Name

And SSN. How can I do a join that allows A and B to join to C.

Can I just to a Left outer from A to C

And a Left outer from B to C.

also, how can I do an left outer join with two tables and two fields. Example:

Select * from A

Left out join (select * from C) u

ON ( t.first_name = u.first_name and

t.Las_name = u.Last_ name)

[former_member731159]

How would I do this in Crystal.

I know how to do it with native SQL, but how would I graphically do this so that crystal would use two fields to perform a Left outer join.

Currently, when I link two tables in Crystal, I can only do a Left Outer Join with one field.

Thanks...

[DellSC]

As I said in my previous answer, link A >> C >> B. This means delete the link you have from B to C and then redraw it starting at C and drawing to B. Then make the link from C to B a Right Outer join instead of Left Outer.

-Dell

[former_member731159]

Sounds good.

Thank you very much...

[former_member731159]

Dell,

You answered my big question without even knowing it. I wasn't aware that all you had to do is add another link when performing a join for two fields . Example: ON (t.field1 = u.field1 AND t.field2 = u.field2). I looked back over your answers and it became clear that I could do this.

Thaaaaaaaaaaaaaaaaaaaaank you, for your help...