Left outer join not working like I think it should

[cathyg18]

Using Crystal 7, I am struggling with this scenario.

TABLE1 TABLE2

Client -------> Client
Account ------> Account
OtherField1 Table2Field
OtherField2

This is a left outer join. I want everyone in Table1 regardless if they show up in Table2. However, I only want those in Table2 where Table2Field = 'CRITERIA'.

({TABLE2.TABLE2FIELD} = "CRITERIA" or isnull ({TABLE2.TABLE2FIELD}) or {TABLE2.TABLE2FIELD} = '')

still only pulls records from TABLE1 that exist in TABLE2.

Can someone help before ALL my hair is snatched out?

Thanks.

Cathy

 

[Madawc]

I use Crystal 8.5, but I've had something similar. If you have a left-outer relationship but also do a selection, then it gets treated as if it were an equals relationship.

It's also best to test for null before making any other test, because commands stop when they find a null. But I think that in this case it makes no difference.

In Crystal 8.5, I'd do a field-suppress on the Table2 values if they are not = 'CRITERIA'.

Madawc Williams (East Anglia, UK)

 

[lbass]

You cannot select on the right hand table, so remove the criteria from your record selection and then use a formula like:

if isnull({table2.field}) or
{table2.field} <> <your criteria> then "" else
{table2.field}

-LB