Re: How? Join twice against same table?

Stephen C. Hill (shill3_at_garnet.msen.com) wrote:

: 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") )

this work for me:

SQL> select FIELD_A1, b.FIELD_B2, c.FIELD_B2   2 from aaa a, bbb b, bbb c
  3 where a.FIELD_A2_REFB1 = b.FIELD_B1   4 and a.FIELD_A3_REFB1 = c.FIELD_B1;

FIELD_A1 FIELD_B2 FIELD_B2
---------- ---------- ----------
A1 B1 B2

Chao-Ping Chien Received on Tue Dec 06 1994 - 23:10:10 CET