How to LEFT JOIN in DB2 iseries with first row?

Question

I have need a query that JOIN a TABLE with A first row of other table value based:

 SELECT * FROM TABLEA A LEFT JOIN
    (SELECT * from TABLEB
       WHERE FIELD1 <> '3' and FIELD2 = 'D' AND A.CODE=CODE
      FETCH FIRST 1 ROW ONLY
     ) B
 on a.FIELDA = b.FIELDA
 and A.FIELDB = B.FIELDB

but DB2 return ERROR because can't use A.CODE

How can solve this?

You are referring to tablea (aliased as A) in your derived table, but you don't have it in the from clause. I think you need to move it to your join clause. — Andrew, Nov 04 '13 at 18:01
possible duplicate of [Select the first row in a join of two tables in one statement](http://stackoverflow.com/questions/774769/select-the-first-row-in-a-join-of-two-tables-in-one-statement) — bluish, Apr 07 '14 at 14:43

score 11 · Answer 1 · answered Nov 04 '13 at 23:41

11

You need to use the nested table expression:

SELECT * FROM TABLEA A LEFT JOIN
  LATERAL (SELECT * from TABLEB
     WHERE FIELD1 <> '3' and FIELD2 = 'D' AND A.CODE=CODE
     FETCH FIRST 1 ROW ONLY
  ) B
on a.FIELDA = b.FIELDA
and A.FIELDB = B.FIELDB

answered Nov 04 '13 at 23:41

mustaccio

18,234
16
48
57

I might of down voted I don't remember. Down votes cost reputation so don't worry about it. Where exists will easily out perform a fetch first 1 row only statement. – danny117 Mar 17 '14 at 02:41
To be fair the `where exists` is not the same or equivalent of the query in the question as it only returns columns from TABLEA and not TABLEB. – Ben Thurley Mar 18 '16 at 10:53

score 1 · Answer 2 · answered Nov 05 '13 at 01:04

1

This is a highly optimized statement. Your not getting any data from tableb and your going for first row so you just need exists clause.

select a.* from tablea a 
where exists (select * from tableb b 
    where a.fielda = b.fielda 
    and a.fieldb = b.fieldb 
    and b.code = a.code 
    and b.field2 = 'd' and b.field1 <> '3')

answered Nov 05 '13 at 01:04

danny117

5,581
1
26
35

However, since the code said `SELECT *`, we don't know what columns Daniele might be using from TableB – WarrenT Nov 05 '13 at 05:36
@WarrentT That's true. I thought of that and almost changed my answer. But select first one rows only without an order by clause you wouldn't know what rows were actually returned only that a few fields would match. Without knowing more intimate details I went with where exists because I often do these give me Students with at least one course type of queries. – danny117 Nov 06 '13 at 19:41

score 0 · Answer 3 · answered Nov 05 '13 at 05:55

You can use the OLAP function row_number() to rank the records according to somefield(s) within a (fielda,fieldb,code) group. Somefield might be a transaction id, or sequence, for example. The order by clause is optional there, but without it, you might be randomly picking which record is the first in the group.

 WITH B AS
 (SELECT *,
         row_number() over (partition by fielda,fieldb,code
                            order     by somefield
                           ) as pick 
    from TABLEB
    WHERE FIELD1 <> '3'
      and FIELD2 = 'D'
 )
 SELECT * 
   FROM TABLEA A LEFT JOIN B

       on a.FIELDA = b.FIELDA
      and A.FIELDB = B.FIELDB
      and A.CODE   = B.CODE
   where pick=1

How to LEFT JOIN in DB2 iseries with first row?

3 Answers3