Re: Outer Join Quandry

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 30 Oct 2006 03:51:13 -0800
Message-ID: <1162209073.367333.88760@k70g2000cwa.googlegroups.com>

drvice_at_nppd.com wrote:
> I'm having an issue with an outer join query that I'm hoping someone
> can help me with. I have 3 tables; Emps, Quals, and an XRef table. I
> want to be able to see, for a given set of employees, what quals they
> have, and what quals they do not have. I want this to be returned to
> me in one SQL query.
>
> I tried this:
>
> select Emps.EmpName, Quals.QualName, XRef.QualDate from Emps, XRef,
> Quals where Emps.field1 = XRef.field1(+) and XRef.field2 =
> Quals.field2(+);
>
> With this query, I only get records where the employee has the qual...
>
> If I have 10 employees and 10 quals, I want 100 records returned...with
> the QualDate field blank for those employees who do not have the
> qualification.
>
> Help!

My solution is very similar to that posted by Gints Plivna.

Cartesian join between the Emps table and Quals table (do not specify how the tables should be joined) to return all of the possible combinations, and then an outer join between that Cartesian join and the table containing the qualification dates. This is possible using an inline view:
SELECT
  EQ.EMPNAME,
  EQ.QUALNAME,
  X.QUALDATE
FROM
  (SELECT

    Q.QUALNAME,
    E.EMPNAME,
    E.FIELD1

  FROM
    EMPS E,
    QUALS Q) EQ,
  XREF X
WHERE
  EQ.FIELD1=X.FIELD1(+)
ORDER BY
  EQ.EMPNAME,
  EQ.QUALNAME; Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Oct 30 2006 - 05:51:13 CST