CODEWITHSUNDEEP
joindb2subquery
Adam
Adam

Reputation: 103

Join and subselects

I need to rid my iSeries DB2 query of subselects. In the query below, I need to get ALADD1 and ALADD2 (address 1 and 2) from F0116 matched on both ABAN8 and MAPA8 into one record. ABAN8 is the shipping address ID and MAPA8 is the billing address ID. F0116 holds the values for these address IDs.

Current query:

SELECT T1.ABAN8,T2.MAPA8,T3.ALADD1,T3.ALADD2,
(SELECT ALADD1 FROM F0116 WHERE MAPA8 = ALAN8) AS ALADD101,
(SELECT ALADD2 FROM F0116 WHERE MAPA8 = ALAN8) AS ALADD201
FROM F0101 T1
LEFT JOIN F0150 T2 ON ABAN8 = MAAN8
LEFT JOIN F0116 T3 ON ABAN8 = ALAN8

The result looks like this:

ABAN8,  MAPA8,  ALADD1,             ALADD2,     ALADD101,           ALADD201,
100,    200,    555 SHIPPING STR,   SUITE B,    555 BILLING STR,    SUITE C,
101,    201,    556 SHIPPING STR,   SUITE B,    556 BILLING STR,    SUITE C,
102,    202,    557 SHIPPING STR,   SUITE B,    557 BILLING STR,    SUITE C

But this is a very simplified example. There are actually six address fields, so efficiency is horrible. How can I accomplish this without the subselects for each of the address 2 fields?

Thanks in advance.

Upvotes: 0

Views: 69

Answers (1)

bhamby
bhamby

Reputation: 15499

Since you didn't really include a schema, and the names don't help at all, I just made a couple of assumptions on which fields do what (they're probably wrong).

You can just add the table a second time in a LEFT JOIN, and pull the addresses that way, like this:

SELECT
     T1.ABAN8
    ,T2.MAPA8
    ,SHIP.ALADD1
    ,SHIP.ALADD2
    ,BILL.ALADD1
    ,BILL.ALADD2
FROM F0101 T1
LEFT JOIN F0150 T2
  ON T1.ABAN8 = T2.MAAN8
LEFT JOIN F0116 AS SHIP
  ON T1.ABAN8 = SHIP.ALAN8
LEFT JOIN F0116 AS BILL
  ON T1.ABAN8 = BILL.ALAN8

Upvotes: 1

Related Questions