Left Outer Join?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=454
Printed Date: 29 Apr 2024 at 10:46am
Topic: Left Outer Join?
Posted By: JDodd
Subject: Left Outer Join?
Date Posted: 03 Apr 2007 at 8:11am
|
Hi All, I have a situation that keeps coming up and I have not found a solution. How do you handle the situation where there may or may not be a records in a joined file. For example I have a claim file that has an injury code in it. It is not mandatory to enter an injury code yet I want to produce a report grouped by injury code and have thsoe with no injury code show up on the report as unknown. I did a left outer join on the injury code file so I could find a description for those that exist but the claism with no injury code are not showing up n the report.
Any help would be greatly appreciated.
Joe
|
Replies:
Posted By: BrianBischof
Date Posted: 03 Apr 2007 at 11:01am
|
Click on the join properties in the Data Expert link tab. You can specify to "Show All" records from the left table. Thus giving you a left outer join.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: JDodd
Date Posted: 03 Apr 2007 at 11:59am
|
Hi Brian,
I'm on version 11 and when I go to the link options in the database expert I have the choice of Inner, Left Outer Rigt Outer, Not Enforced, Enforced From Enforced To, Enforced Both, then a link type. I have Left Outer, Not Enforce, and = checked. Am I in the correct place?
|
Posted By: BrianBischof
Date Posted: 03 Apr 2007 at 12:01pm
|
yes. that should be what you need.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: JDodd
Date Posted: 03 Apr 2007 at 12:11pm
| Could it be the order of my links? Should you link tables going from many to one or one to many records? |
Posted By: dmunoz
Date Posted: 26 Apr 2007 at 8:07am
I have this same problem and i have my join and links the same way and it still is not working. Did you ever get a solution to this problem? If you did I would love to know what you did!
much thanks
dmunoz
|
Posted By: ckreds
Date Posted: 15 Nov 2007 at 5:53pm
| im having same problem...anyone could help? thks. |
Posted By: jkwrpc
Date Posted: 15 Nov 2007 at 7:26pm
|
If I am following the question here is an explanation about joins
Table One = Left Table Table Two = Right Table
To get all the records from table one regardless of whether or not related data exists in table two you use a left outer join. By doing this you will get all the records in Table One (left table) and where it exists the related data in Table two (right table). A right outer join will reverse the logic.
You can use two command objects in CR to return from each table then use the Database Expert Linking tool for the links. You also could use a single command object and create the left outer join in the SQL Query. Regardless of method you will need to be certain you are using the correct fields for the link.
Finally you can find a discussion of left outer joins in the Crystal Reports help files.
Hope this helps.
John W.
http://www.CustomReportWriters.net - www.CustomReportWriters.net
|
Posted By: Lugh
Date Posted: 16 Nov 2007 at 7:29am
|
As a further note, you need to be careful about your selection criteria with outer joins. For instance, suppose you created a left outer join between Claims and Injuries. You want to show all Claims, plus any Injuries with, say, an InjuryAmount greater than 5000. If you just put InjuryAmount > 5000 in your selection criteria, you have suddenly, effectively, made it an inner join. Because it will only return records in which there is an InjuryAmount, and hence only those Claims that have Injuries. To fix this, make sure to allow for null values in your selection criteria. If a matching record is not found for the left join, all the values for the Injuries table are returned as null. So, your selection criteria should look something like: (InjuryAmount > 5000) OR IsNull(InjuryAmount) Now, unfortunately, this will filter out any Claims that do have a matching record in the Injuries table, but not one with an InjuryAmount greater than 5000. Those Claims just won't be shown. To solve that, you need a conditional join, and Crystal, unfortunately, doesn't support those natively. |
Posted By: kashmarsh
Date Posted: 11 Jul 2008 at 4:43pm
|
Hello all, I have 8.5 version. I cannot do a left outer join. I have 3 tables however so not sure how to determine which table is on the left side. I did try it anyway, with the same results.
I went to Visual Linking Expert to do this. Where else can I force Crystal to show me all records regardless if it finds a match on another table?
Thanks in advance.
|