self outer join problem: please assist

From: BillRSTL <theshowmecanuck_at_123nospam_netscape.net>
Date: Thu, 16 Jan 2003 20:23:20 GMT
Message-ID: <3e27134b$0$57793$9a6e19ea@news.newshosting.com>

Hey there,         

	I am trying to do a self join to some table with a number of extra
	conditions on the second iteration of the self join.  To better phrase the
	question I am posting an example (with tables and data) of what I would
	like to get as a result, my current (incorrect) query, and its result. 
	This on Oracle 8i.

What I would like to see returned:

ACCT	SA_TWO      LOOKUP_ONE          ACCT_JOIN2      LOOKUP_ONE_JOIN2
1              P                      LARRY_NC                1
              BOB_C
2              P                      JUDY_NC                   2
                BOB_C
2              P                      JUDY_NC                   2
                GRACIE_C
3              P                      FRANK_NC               NULL
       NULL
4              P                      JUDY_NC                   4
                GRACIE_C
5              P                      FRANK_NC               NULL
       NULL

Here is my current query:

select a1.ACCT, a1.SA_TWO, a1.LOOKUP_ONE, a2.ACCT, a2.LOOKUP_ONE

from agreement a1,
     agreement a2,
     account acct,
     myfeature mf,
     lookup l

where a1.acct = acct.acct

and a1.sa_two = 'P'
and a1.acct = a2.acct(+)
and a2.lookup_one = l.lookup_one

and l.feature_id = mf.feature_id
and mf.description = 'CAR'
order by a1.acct

This is what this current incorrect query returns:

ACCT    SA_TWO       LOOKUP_ONE      ACCT_1        LOOKUP_ONE_1
1             P                        LARRY_NC           1
    BOB_C
1             P                        LARRY_NC           1
    BOB_C
2             P                        JUDY_NC              2
      BOB_C
2             P                        JUDY_NC              2
      BOB_C
2             P                        JUDY_NC              2
      GRACIE_C
4             P                        JUDY_NC              4
      GRACIE_C

What the heck am I doing wrong? Tables to go with the example.

CREATE TABLE AGREEMENT(

       ACCT INT,
       SA_TWO CHAR(1),
       LOOKUP_ONE CHAR(10)
       );

CREATE TABLE LOOKUP(
       LOOKUP_ONE CHAR(10),
       FEATURE_ID INT
       );

CREATE TABLE ACCOUNT(
       ACCT INT
       );

CREATE TABLE MYFEATURE(
       FEATURE_ID INT,
       FEATURE_MARKET INT,
       DESCRIPTION CHAR(10)
       );

INSERT INTO ACCOUNT ( ACCT ) VALUES (
1);
INSERT INTO ACCOUNT ( ACCT ) VALUES (
2);
INSERT INTO ACCOUNT ( ACCT ) VALUES (
3);
INSERT INTO ACCOUNT ( ACCT ) VALUES (
4);
INSERT INTO ACCOUNT ( ACCT ) VALUES (
5);
commit;

INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 1, 'P', 'LARRY_NC');
INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 1, 'R', 'BOB_C');
INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 2, 'P', 'JUDY_NC');
INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 2, 'R', 'BOB_C');
INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 2, 'R', 'GRACIE_C');
INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 3, 'P', 'FRANK_NC');
INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 4, 'P', 'JUDY_NC');
INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 4, 'R', 'GRACIE_C');
INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 5, 'P', 'FRANK_NC');
INSERT INTO AGREEMENT ( ACCT, SA_TWO, LOOKUP_ONE ) VALUES ( 4, 'R', 'LARRY_NC');
commit;

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'BOB_C', 10);

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'BOB_C', 20);

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'GRACIE_C', 10);

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'LARRY_NC', 30);

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'GRACIE_C', 30);

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'LARRY_NC', 40);

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'BOB_C', 40);

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'JUDY_NC', 40);

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'JUDY_NC', 30);

INSERT INTO LOOKUP ( LOOKUP_ONE, FEATURE_ID ) VALUES (
'FRANK_NC', 50);

commit;

INSERT INTO MYFEATURE ( FEATURE_ID, FEATURE_MARKET, DESCRIPTION ) VALUES ( 10, 200, 'CAR');
INSERT INTO MYFEATURE ( FEATURE_ID, FEATURE_MARKET, DESCRIPTION ) VALUES ( 20, 300, 'CAR');
INSERT INTO MYFEATURE ( FEATURE_ID, FEATURE_MARKET, DESCRIPTION ) VALUES ( 30, 200, 'BUS');
INSERT INTO MYFEATURE ( FEATURE_ID, FEATURE_MARKET, DESCRIPTION ) VALUES ( 40, 200, 'TRAM');
INSERT INTO MYFEATURE ( FEATURE_ID, FEATURE_MARKET, DESCRIPTION ) VALUES ( 50, 200, 'TRUCK');
commit;         Received on Thu Jan 16 2003 - 14:23:20 CST