Re: How? Join twice against same table?

In article <3c1s72$j66$2_at_heifetz.msen.com>, shill3_at_garnet.msen.com (Stephen C. Hill) writes:

:How do I get a record back that shows 2 independent fields
:from a second table ?
:Table AAA: CREATE TABLE AAA (
: field_A1 VARCHAR2(10),
: field_A2_RefB1 NUMBER,
: field_A3_RefB1 NUMBER
: );
:
:
:Table BBB: CREATE TABLE BBB (
: field_B1 NUMBER UNIQUE NOT NULL,
: field_B2 VARCHAR2(10)
: );
:
:
:Fields field_A2_RefB1 and field_A3_RefB1 reference field_B1
:
:
:Data of Table AAA: "A1", 1, 2
:
:Data of Table BBB: 1, "B1"
: 2, "B2"
:Relation: Table AAA: "A1", 1, 2
: | |
: | |
: Table BBB: | +-> 1, "B1"
: +----> 2, "B2"
:
:
:Desired output: A1, B1, B2
:
 

:Select statement like:
:
: SELECT field_A1,
: DECODE(field_A2_RefB1, SELECT-stmt ??),
: DECODE(field_A3_RefB1, SELECT-stmt ??)
: FROM AAA, BBB
: WHERE ...

:NO hard-coding within DECODE wanted !
: (like DECODE(field_A2_RefB1, 1, "B1", 2, "B2") )

How about a 3 table join ??

SELECT field_A1, X.field_B2, Y.field_B2 FROM AAA, BBB X, BBB Y
WHERE field_A2_RefB1 = X.field_B1
AND field_A3_RefB1 = Y.field_B1;

The X and Y can be used as aliases so that you can join against one table twice....

Lee Received on Wed Dec 07 1994 - 06:20:21 CET