Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Outer Join Quandry

Re: Outer Join Quandry

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: 30 Oct 2006 03:33:23 -0800
Message-ID: <1162208003.487145.42280@m73g2000cwd.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!

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 Received on Mon Oct 30 2006 - 05:33:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US