Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Outer Join Quandry
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!
Wht does XRef table does?
Not sure, but if you want employees and their respective Quals, then
this can be received using
SELECT column_name
FROM Emps LEFT OUTER JOIN Quals on (joining condition)
This will give all employees from EMPS table whether or not they have any Quals in Quals table Received on Mon Oct 30 2006 - 03:50:23 CST