Re: How? Join twice against same table?

From: Don Vick <dvick_at_lanier.com>
Date: Tue, 6 Dec 1994 20:06:31 GMT
Message-ID: <D0Enuw.ACJ_at_lanier.com>

In article <3c1s72$j66$2_at_heifetz.msen.com>, 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

Try this: select aaa.field_A1, X.field_B2, Y.field_B2

	   from aaa, bbb X, bbb Y
	   where aaa.field_A2_RefB1=X.field_B1
	     and aaa.field_A3_RefB1=Y.field_B1;

The X and Y (called aliases) allow you to refer to two separate "copies" of the same table. All sorts of interesting effects are possible.

BTW, the graphical form you used to draw the output relation is an excellent tool for writing such queries. Each arrow translates to a join condition (part of the 'where' clause), and the "B1" and "B2" following the arrowheads are the additional items in the 'select' list.

---

Donald E. Vick (dvick_at_lanier.com, dvick_at_crl.com) Voice: (404) 493-2194 Fax: (404) 493-2399 Received on Tue Dec 06 1994 - 21:06:31 CET