Lef Join with "OR"/"IN"

From: halbert <hjls_at_optonline.net>
Date: 22 Nov 2001 07:55:32 -0800
Message-ID: <6857987e.0111220755.58ef8a35@posting.google.com>

What is the syntax for Oracle 8i SQL+ to do a left join of say an employee table and a dependent table? I need to return several columns from each table, and return all employees whether they have qualifying dependents or not. The tables are related via employee.SSN. I only want to return employees and dependents where the employee has an employee.STATUS=1. Also, I only want to return dependents who are spouses, that is where dependent.spousecode = 3 OR 4.

The following appears to work, but when I change it to SPOUSECODE(+) IN(3, 4) it fails. I get a message something like "can't use IN or OR operator with outer join".

SELECT * FROM DEPENDENT,EMPLOYEE

	WHERE DEPENDENT.ESSN(+) = EMPLOYEE.ESSN
	AND   EMPLOYEE.STATUS = 1
        AND   DEPENDENT.SPOUSECODE(+) = 3

Do I need a subquery? Thanks for any help as I am new at this.

Hal Received on Thu Nov 22 2001 - 09:55:32 CST