Left Outer Join Filter or Syntax

[dande]

Working with an ODBC connection to SQL2k data. How can I report only records from the primary table that do not have a related record in the linked table. I can report all records with the left outer join just fine, but I need to report only the primary table records without a related record.

Thanks

 

[lbass]

After creating the left join from the primary table (TableA) to the secondary table (TableB), use the following formula for your record select:

isnull({TableB.ID}) //where {TableB.ID} is the field you are linking on

-LB

 

[dande]

No records reported. The relationship of table A to B is a 1 to many. Does that matter (I don't know why it should)? Here is the syntax I entered.
isnull({activwork.clientcode_c}) This is the B table with a Left join linked to clientcode_c.

 

[lbass]

As long as activwork is the B table, and the link is from the A table to the B table, this should work. Are you sure that there are cases where {tableA.clientcode_c} is not null, but where {activwork.clientcode_c} is null? Is the clientcode_c field found in all TableA records? The name makes it sound like a subset of records. Usually you would link on some sort of unique ID field.

-LB

 

[dande]

Everything is as you asked. Table A (Client) is linked from the unique field that doesn't contain any nulls. Table B (Activites) is linked to the Client unique id. When I run the report without any record selections defined, I get a good left join report - because I get data from the both files and then only data from the Client file and nothing from the Activities file where there is no corresponding record. Could the problem be that the application feeding the data is through a Borland BDE connection and Crystal is accessing via a SQL ODBC connection??? I'm really puzzled.

Paul

 

[lbass]

I can't answer your question about the database connections--maybe someone else can.

Is it possible that the field in TableB (Activwork) is not null? You could test this by adding a field to the report (before using any record selection criteria):

isnull({activwork.clientcode_c})

If this returns false for records where there is no apparent record in Activwork, then maybe you have blanks in that field. Then you could try the following for your record selection criteria:

isnull({activwork.clientcode_c}) or
trim({activwork.clientcode_c}) = ""

-LB

 

[dande]

LB - Thanks for your time. Already tried your logic with same results of isnull attempt - nothing is reported. If I discover a solution I will reply here.

Thanks Again
Paul

 

[dande]

LB -
len(trim({activwork.clientcode_c}))= 0 was the trick that worked!

 

[lbass]

Thanks for letting me know. It's funny that:

trim({activwork.clientcode_c}) = ""

...didn't work. When you tried this, did you make sure there was no space between the quotes? Even though the "is null" part of my suggestion wasn't necessary (since the field wasn't null after all), it shouldn't have prevented the second part of the formula from evaluating. In other words, I think that:

trim({activwork.clientcode_c}) = ""

is equivalent to:

len(trim({activwork.clientcode_c})) = 0

-LB

 

[dande]

You're correct it does work. Had to have been a syntax error on my part.