How to modify a LEFT OUTER JOIN

[swc123]

Hi,
Is there a way to create a conditional LOJ? I know the joins reside in Visual Linking. I have the following parent / child relationship and want all parent records but don't want all the children records (only those with Fieldb = '2') Ex.

P.Fielda C.Fielda C.Fieldb
REC1 REC1 1
REC1 2
REC1 3
REC2 REC2 1
REC3 REC3 2
REC4 REC4 1
REC4 REC4 2

desired outcome
REC1
REC2
REC3
REC4

If I add Fieldb='2' into the Record Selection then desired records are omitted. If I perform a LOJ on the key field then I get repeated records(the nature of LOJ). I can accomplish this via sql but want to try keeping everything in Crystal. After writing this I suppose I could create a subreport for the field.

sql: LEFT OUTER JOIN child ON
parent.fielda = child.fielda AND
child.fieldb = 2

provides the 3 records above.

Thanks in advance

 

[synapsevampire]

Maybe, that depends on your Crystal version, database and connectivity used. Posting without stating versions always surprises me, I guess many people have never called a technical support line before and learned the very first things that would be asked to help someone.

If you know or can find this information someone can better help you.

You aren't using a Left Outer properly, which is part of the problem. Stating that you want all rows from the parent, but only rows from the child with a condition contradicts itself when there's a Left Outer. A subquery can handle this, but you need CR 9 to paste in your own SQL effectively.

You might create conditionals on each column as SQL Expressions that would return null for those rows which do not satisfy your filtering, but suppression is the quickest way home, or using a subreport, but the most efficient would be to write Views or SP's which use a subquery on the database side.

You can also try:

(
Fieldb='2'
or
isnull(Fieldb)
)

-k

 

[lbass]

Another approach would be to use the left join without any selection on {child.fieldb}. Then create a formula {@child}:

if isnull({child.fieldb}) or
{child.fieldb} <> 2 then &quot;&quot; else
totext({child.fieldb},0,&quot;&quot

Then group on {parent.fielda} and insert a maximum on {@child}. This will display all parent fields and only those child fields equal to 2.

-LB

 

[swc123]

Thank you both and as with anything in IT there are always many ways to perform the same task. Knowledge and skills (most) improve with time.

SV-> You're right regarding versions & should remember having done support. Regarding LOJ, from the user's guide I read it as &quot;include those EQUAL, and those with parents but no matching child (our 'match' is if it's a 2)&quot;. I was reinforced when it worked under sql (MS sql).

Thanks again.