Table join problem?

[AcctSolver]

I am using Crystal 2008. I have two tables. First table has all employees. Second table has their banking information. I want to obtain the population of all employees with no banking information. I have links on employee#, SSN, etc.

What kind of join should these tables/links have? I have tried excluding various fields from the banking table in my selection statement and still wound up with a record for each employee in the employee table (of course). I only want a record to return from the employee table when there is no record in the banking table. Where did I go wrong?

 

[lbass]

Use a left join FROM the employee table TO the banking table, and then use a record selection formula like this:

isnull({banking.ssn})

In other words, you are requiring the linking field in the banking table to be null.

-LB

 

[AcctSolver]

This is what the SQL looks like: "SELECT V_EMPLOYEE.FILE#, V_EMPLOYEE.SOCIALSECURITY#, V_EMPLOYEE.NAME, V_BANKING_INFO.SOCIALSECURITY#, V_EMPLOYEE.COMPANYCODE, V_EMPLOYEE.STATUS
FROM REPORTS.V_EMPLOYEE V_EMPLOYEE, REPORTS.V_BANKING_INFO V_BANKING_INFO
WHERE (((V_EMPLOYEE.COMPANYCODE=V_BANKING_INFO.COMPANYCODE (+)) AND (V_EMPLOYEE.FILE#=V_BANKING_INFO.FILE# (+))) AND (V_EMPLOYEE.SOCIALSECURITY#=V_BANKING_INFO.SOCIALSECURITY# (+))) AND V_EMPLOYEE.COMPANYCODE='ABC' AND (V_EMPLOYEE.STATUS='A' OR V_EMPLOYEE.STATUS='L') AND V_BANKING_INFO.SOCIALSECURITY# IS NULL". I wound up with all employees listed. I did put in a left outer join, not enforced, link type =.
The selection statement looked like this.
{V_EMPLOYEE.COMPANYCODE}='ABC'
and {V_EMPLOYEE.STATUS} in ["A","L"]
and isnull({V_BANKING_INFO.SOCIALSECURITY#})

I know I missed something - will you please help me find it?

 

[lbass]

This looks correct to me. Go into file->report options and make sure that "convert nulls to default values" is not checked. Also add at least one field from each table to the report.

-LB