Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Left Outer Join? Post Reply Post New Topic
Author Message
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet Topic: Left Outer Join?
    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
 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet 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?
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet 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?
IP IP Logged
dmunoz
Newbie
Newbie


Joined: 26 Apr 2007
Online Status: Offline
Posts: 2
Quote dmunoz Replybullet 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!Smile
much thanks
dmunoz
IP IP Logged
ckreds
Newbie
Newbie


Joined: 24 Sep 2007
Location: Malaysia
Online Status: Offline
Posts: 16
Quote ckreds Replybullet Posted: 15 Nov 2007 at 5:53pm
im having same problem...anyone could help? thks.
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet 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.
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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.
IP IP Logged
kashmarsh
Newbie
Newbie


Joined: 11 Jul 2008
Online Status: Offline
Posts: 1
Quote kashmarsh Replybullet 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.


Edited by kashmarsh - 11 Jul 2008 at 4:44pm
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.