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.

Thanks!

with theMinimumOnes as (
select personId,
       min(emailType) as emailType  
  from PersonEmail
 group by personId
)
select p.personId,
       p.firstName,
       p.lastName,
       pe.emailAddress
  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

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Member_2_2484401

ASKER

Good morning, Kent! As usual, you've absolutely nailed it. I knew there had to be a better way. Thanks!

select p.personId,                      
       p.firstName,                     
       p.lastName,                      
       em.emailAddress                  
  FROM Person p                   
  LEFT OUTER JOIN PersonEmail em  
    on em.PersonId = p.PersonId         
   and em.EmailType = (                 
       SELECT min(EmailType)            
         FROM PersonEmail pe      
        WHERE pe.PersonId = em.PersonId)

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

Member_2_2484401

ASKER

Thanks, Kent!