LEFT OUTER JOIN in Crystal

[StevenK]

It's been an age since I've used Crystal Reports and it seems I've forgotten some of the basics.

I've got 2 tables (in an SQL Server database) - a header and detail table (let's call them HEAD and DETAIL).

I've set these up via the 'Database Expert' - 'Links' option linking the 2 tables with a 'Left Outer Join'.
If I then set up a 'Report' - 'Select Expert' selection criteria against the DETAIL table (i.e. "DETAIL_TYPE = 'TYPE1'") I then find that when I run my report I onlt get details of those HEAD entries where I have associated entries in the DETAIL table with a DETAIL_TYPE of 'TYPE1'.
If no DETAIL entries exist with this type then the associated HEAD entry is not output.

How (in Crystal Reports) can I ensure that I get a full list of all HEAD records - regardless of whether we have any DETAIL entries like this?
I want to set up a group (per HEAD entry) and then list all entries in DETAIL with a DETAIL_TYPE of 'TYPE1' - if there are none (for the HEAD entry) then the detail section would be blank - but the group header / footer should still show.

Can anyone suggest how this can be done?
Thanks in advance,
Steve

 

[Turkbear]

Hi,
Any criteria applied to the 'right' table in a left outer join causes the join to be changed to an equal join and will produce the result you are seeing.

One solution. depending on your database and your access rights, would be to create a view in the database of the Details table with just TYPE1 details..Use that View as the 'right' table.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[StevenK]

I can see that the creation of a VIEW object would resolve this - but is there any means to be able to do what I need to in Crystal?

I guess that I could make use of a subreport for the detail elements (setting the 'Selection' clause against this).
If I do this - will the report take a lot longer to run ?

Steve

 

[Turkbear]

Hi,
Subreports are always ( well, mostly) less efficient than using the database to pre-create the data in a format you need..
There are other methods to solve the left-outer issue - search this forum for 'LEFT-OUTER' and take a look at some of the ideas


By the way, this is not a Crystal issue, it is the way SQL
behaves...



To Paraphrase:"The Help you get is proportional to the Help you give.."