Question Re: LEFT outer join - pains in Technology Q&A

LEFT outer join - pains

former_member731159 — Tue, 20 Apr 2021 20:36:32 GMT

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

Re: LEFT outer join - pains

DellSC — Tue, 20 Apr 2021 21:04:04 GMT

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

-Dell

Re: LEFT outer join - pains

former_member731159 — Tue, 20 Apr 2021 22:50:36 GMT

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)

Re: LEFT outer join - pains

former_member681242 — Wed, 21 Apr 2021 07:11:50 GMT

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

Re: LEFT outer join - pains

DellSC — Wed, 21 Apr 2021 15:30:09 GMT

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

Re: LEFT outer join - pains

former_member731159 — Wed, 21 Apr 2021 17:52:58 GMT

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...

Re: LEFT outer join - pains

DellSC — Wed, 21 Apr 2021 18:51:43 GMT

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

Re: LEFT outer join - pains

former_member731159 — Wed, 21 Apr 2021 23:27:29 GMT

Sounds good.

Thank you very much...

Re: LEFT outer join - pains

former_member731159 — Thu, 22 Apr 2021 15:47:52 GMT

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...