CODEWITHSUNDEEP
sqlteradata
Kornél Regius
Kornél Regius

Reputation: 3049

SQL - join, if multiple rows then choose based on column data

I am joining two tables (Account,Address) based on a column called acc_id

The Address table sometimes have multiple addresses associated to an acc_id, and they are distinguished with a type column, that marks if the address is a primary or a secondary address.

I would like to join the two tables on the acc_id, the Address table's columns should be the following in the result:

How to do this in (Teradata-)SQL?

Upvotes: 1

Views: 5228

Answers (2)

BellevueBob
BellevueBob

Reputation: 9618

Here is a much simpler Teradata solution:

SELECT   a.acc_id
       , b.*
FROM     account a
LEFT JOIN address b
ON      b.acc_id=a.acc_id
QUALIFY ROW_NUMBER() OVER (PARTITION BY a.acc_id
                           ORDER BY     b.address_type) = 1

QUALIFY is a Teradata extension and very handy for things like this. A partition is built using the PARTITION BY clause and sorted by the ORDER BY clause. Then QUALIFY will keep the first row in each partition using the ROW_NUMBER function.

Of course, I'm assuming that your address_type column has values that sort as you want. If the values are in fact "Primary" and "Secondary", sorting in ascending order as shown should work (selecting "Primary" when both values exist). You can also use a CASE expression in the ORDER BY clause if needed.

I'd also strongly suggest explicitly naming the columns wanted for your address table. Your comment about wanting "nulls" for no addresses implies you need a LEFT JOIN.

Upvotes: 2

ChrisCamp
ChrisCamp

Reputation: 682

case statements could work for you!

  • do a left join on the "Address" table twice.
  • One of the join conditions should be for the "primary" address
  • and the other for the secondary address.
  • The case statements first check for the primary address
  • and if one doesn't exist
  • then select the secondary address.
  • Since a left outer join is being used if neither exists the value will show as NULL
      SELECT DISTINCT a.acc_id
           , CASE WHEN primary_add.acc_id IS NOT NULL 
                  THEN primary_add.address 
                  ELSE secondary_add.address
             END address
           , CASE WHEN primary_add.acc_id IS NOT NULL 
                  THEN primary_add.city
                  ELSE secondary_add.city
             END city 
           , CASE WHEN primary_add.acc_id IS NOT NULL 
                  THEN primary_add.state
                  ELSE secondary_add.state
             END state 
           , CASE WHEN primary_add.acc_id IS NOT NULL 
                  THEN primary_add.zip
                  ELSE secondary_add.zip
             END zip 
           , CASE WHEN primary_add.acc_id IS NOT NULL 
                  THEN primary_add.address_type
                  ELSE secondary_add.address_type
             END address_type
          ------------------------------------------------
         FROM account AS a
          ------------------------------------------------
         LEFT OUTER JOIN address AS primary_add 
           ON     a.acc_id = primary_add.acc_id 
              AND address_type = 'primary'
          ------------------------------------------------
         LEFT OUTER JOIN address AS secondary_add 
           ON     a.acc_id = secondary_add.acc_id 
              AND address_type = 'secondary'

Upvotes: 2

Related Questions