Avatar of Member_2_2484401
Flag for United States of America

asked on 

DB2 return first match

In DB2 for i (a.k.a. DB2/400) at V6R1, I want to write a SELECT statement that returns some columns from a header record and some columns from ONLY ONE of the matching detail records. It can be ANY of the matching records, but I only want info from ONE of them. I am able to accomplish this with the following query below, but I'm thinking that there has to be an easier way than using a WITH clause. I'll use it if I need it, but I keep thinking, "There must be an easier way". Essentially, I'm just returning the firstName and lastName from the Person table ... plus ONE of the matching email-addresses from the PersonEmail table.


with theMinimumOnes as (
select personId,
       min(emailType) as emailType  
  from PersonEmail
 group by personId
select p.personId,
  from Person p
  left outer join theMinimumOnes tmo
    on tmo.personId = p.personId
  left outer join PersonEmail pe
    on pe.personId = tmo.personId
   and pe.emailType  = tmo.emailType

    PERSONID   FIRSTNAME                       LASTNAME                        EMAILADDRESS
           1   Bill                            Ward                            p1@home.com 
           2   Tony                            Iommi                           p2@cell.com 
           3   Geezer                          Butler                          p3@home.com 
           4   John                            Osbourne                        -           

Open in new window

DB2IBM System i

Avatar of undefined
Last Comment

8/22/2022 - Mon