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!

drvice (AT) nppd (DOT) com wrote:

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

drvice (AT) nppd (DOT) com wrote:

Actually it seems you need cartesian join between Emps and Quals.
Generally it will be rather monstrous result set.
Here is my example:

SQL> create table users (usr_id number, username varchar2(100));

Table created.

SQL> create table quals (qal_id number, qal_name varchar2(100));

Table created.

SQL> create table user_quals (usq_usr_id number, usq_qal_id number,
usq_date date);

Table created.

SQL> insert into users select rownum, username from dba_users;

42 rows created.

SQL> insert into quals select rownum, role from dba_roles;

31 rows created.

SQL> insert into user_quals values (1, 1, sysdate);

1 row created.

SQL> insert into user_quals values (1, 10, sysdate);

1 row created.

SQL> insert into user_quals values (10, 10, sysdate);

1 row created.
SQL> ed
Wrote file afiedt.buf

1 SELECT username, qal_name, usq_date
2 FROM (
3 SELECT * FROM users, quals) cartj,
4 user_quals
5 WHERE cartj.usr_id = usq_usr_id (+)
6* AND cartj.qal_id = usq_qal_id (+)
SQL> /

USERNAME
-------------------------------------------
QAL_NAME
-------------------------------------------
USQ_DATE
-------------------
SYS
CONNECT
2006-10-30:15:20:45

SYS
RESOURCE


SYS
DBA


SYS
SELECT_CATALOG_ROLE


SYS
EXECUTE_CATALOG_ROLE


SYS
DELETE_CATALOG_ROLE


SYS
EXP_FULL_DATABASE


SYS
IMP_FULL_DATABASE


SYS
RECOVERY_CATALOG_OWNER


SYS
GATHER_SYSTEM_STATISTICS
2006-10-30:15:20:57

SYS
LOGSTDBY_ADMINISTRATOR


SYS
AQ_ADMINISTRATOR_ROLE

Gints Plivna
http://www.gplivna.eu

drvice (AT) nppd (DOT) com wrote:
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.

Gints/Charles,

Your solutions worked great for me! Many thanks!

Dan