CODEWITHSUNDEEP
sql
Sy T.
Sy T.

Reputation: 11

sql select preferred mailing address if indicated

I have a customer table and some customers can have two different addresses. One address is marked as Primary and the other is marked as Mailing Only. If the customer has two different addresses, I would like to pull just the one that is marked as Mailing Only. If they have multiple accounts but none indicates Mailings Only, then I want just the Primary.

Name     |  PrefMailings   |   Address

John B.  |  Primary        |   PO Box 123 
John B.  |  Mailing Only   |   123 Street 
Tim T.   |  Primary        |   999 MyAddress 
Tim T.   |  Joint          |   999 MyAddress 
Susan    |  Primary        |   987 Home St.

RESULT 

Name     |  PrefMailings   |   Address 

John B.  |  Mailing Only   |   123 Street 
Tim T.   |  Primary        |   999 MyAddress 
Susan    |  Primary        |   987 Home St.

In the WHERE clause, I used "PrefMailings = Primary or Mailing Only" still not getting the preferred "Mailing Only" address. Any suggestion is greatly appreciated for this newby.

Upvotes: 1

Views: 629

Answers (4)

DRapp
DRapp

Reputation: 48139

I would hope that you are actually joining based on something more than just the name, but you know your data better and probably know that already... That said, you could do a a multiple join by the name and ensure you do get all customers too.

select
      c.name,
      case when mo.name is not null then mo.addess
           when p.name is not null then p.address
           when j.name is not null then j.address
           end FinalAddress
   from
      ( select distinct name
           from customers ) c
         left join customers mo
            on c.name = mo.name and mo.prefMailings = 'Mailing Only'
         left join customers p
            on c.name = p.name and p.prefMailings = 'Primary'
         left join customers j
            on c.name = j.name and j.prefMailings = 'Joint'

Ensure your customer table has an index on (name, prefMailings) to optimize the joins. So you start with each customer and join to the customer table again 3 times for each possible mailing address type. If "Mailing Only" exists, grab it... if not, try "Primary" get it... if that fails, then "Joint".

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35593

I realize that what you are about to see may appear strange, but whilst it may look ungainly it has the advantage of only requiring one pass through the table, and hence it may perform better than alternatives needing multiple passes.

Please also note I have expanded the formatting to make it easier to understand the method employed. This syntax is for MySQL, but equivalent concatenation and substring functions exist in all SQL flavours.

SELECT
      name
    , substr(
            MIN(
                  concat(
                        CASE
                              WHEN prefmailings = 'Mailing Only' THEN 1
                              WHEN prefmailings = 'Primary' THEN 2
                              WHEN prefmailings = 'Joint' THEN 3
                              ELSE 9 END
                        , prefmailings
                        )
                  )
            FROM 2
            ) AS prefmailings
    , substr(
            MIN(
                  concat(
                        CASE
                              WHEN prefmailings = 'Mailing Only' THEN 1
                              WHEN prefmailings = 'Primary' THEN 2
                              WHEN prefmailings = 'Joint' THEN 3
                              ELSE 9 END
                        , address
                        )
                  )
            FROM 2
            ) AS prioritized_address
FROM customers
WHERE prefmailings IN ( 'Mailing Only' , 'Primary' )
GROUP BY
      name
;

See this SQLfiddle demo

The WHERE clause is optional by the way, and note that to change priority you simply change the numbers assigned to each mailing type. That priority number could be held in a table or derived table in lieu of the hardcoding seen above.

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

Another way is joining into an inline view that uses conditional aggregation:

select c.*
  from customers c
  join (select sum(case prefmailings
                     when 'Mailing Only' then 1
                   end) as prim,
               name
          from customers
         group by name) v
    on (v.prim = 1
   and c.prefmailings = 'Mailing Only')
    or (v.prim is null
   and c.prefmailings = 'Primary')
   and c.name = v.name

Fiddle: http://sqlfiddle.com/#!2/cdc15/8/0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this in standard SQL using union all and some logic:

select c.*
from customers c
where c.PrefMailings = 'Mailing Only'
union all
select c.*
from customers c
where c.PrefMailings = 'Primary' and
      not exists (select 1
                  from customers c2
                  where c2.Name = c.Name and c2.PrefMailing = 'Mailing Only'
                 ) ;

Upvotes: 1

Related Questions