Can't get records that are in one table, and not another

[benamorton]

I have a membership info (mem_info) table and a contributions table (both hold member numbers). I simply want to show some of the fields from mem_info, where there is not a linked record in contributions table. Currently, I am linking the two tables by member_number, and family ID.

The report is showing all the entries from the mem_info table, regardless of whether or not there is a linked record in the contributions table.

I have tried the outer join feature, and it doesn't seem to work.

I am running Crystal version 8.0

Any help is appreciated,
Ben

 

[lbass]

You should have a left outer join from the member table to the contributions table. Then add the following to your record selection formula:

isnull({contributions.member_number})

This should return only those members who have made no contributions.

-LB

 

[synapsevampire]

Did you try LB's solution?

It should work for you. If not, post obvious details such as the database being used, and if you're using any other selection criteria/filtering.

If you add filtering to the child table, then you'll be overiding the null unless properly constructed.

-k

 

[Turkbear]

Hi,
Using a left outer join may not be the way to go - it will return ALL mem_info records regardless of the existance of a matching record in the contributions table.

Try a NOT EQUAL join, if your database supports this option..
Or,better yet, if possible, create a view that selects the unmatched records and use that instead.