Re: self outer join problem: please assist

this is a total guess since i can't really figure out what this query is supposed to do, but here goes:

select distinct a1.ACCT, a1.SA_TWO, a1.LOOKUP_ONE, a2.ACCT, a2.LOOKUP_ONE
from agreement a1,

     account acct,
(select acct, sa_two, agreement.lookup_one from agreement, myfeature mf, lookup l
where agreement.lookup_one = l.lookup_one and l.feature_id = mf.feature_id
and mf.description = 'CAR') a2
where a1.acct = acct.acct
and a1.sa_two = 'P'
and a1.acct = a2.acct(+)
order by a1.acct

      ACCT S LOOKUP_ONE ACCT LOOKUP_ONE ---------- - ---------- ---------- ----------

         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
         4 P JUDY_NC             4 GRACIE_C
         5 P FRANK_NC

it seems to match the results that you want. other than that i have no idea if it's correct or not.

"BillRSTL" <theshowmecanuck_at_123nospam_netscape.net> wrote in message news:<3e27134b$0$57793$9a6e19ea_at_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 Fri Jan 17 2003 - 15:03:28 CST