CODEWITHSUNDEEP
sqlsql-server
Patrick
Patrick

Reputation: 1

How To Choose One Address from Multiple Address with Conditional

I have table like this :

CustomerID | Address  | Address Type
100000001  | addressA | Mailing Address
100000001  | addressB | Registered Address
100000001  | Null.    | Office Address

One customer could have multiple addresses. I want to select only one address with these conditional situation:

  1. If Office Address is not null, then I will get Address from Office Address. If Office Address is null, then go to number 2.

  2. Check if Registered Address is not null, then I will get Address from Registered Address.

Upvotes: 0

Views: 462

Answers (3)

Rima
Rima

Reputation: 1455

select * from customer 

select  max(customerID) customerID ,max(Address) Address ,max(Addresstype) Addresstype
from customer  
where address is not null
group by customerID

Output

enter image description here

Upvotes: 0

kr153
kr153

Reputation: 282

CREATE TABLE CUSTOMER (CUSTOMERID INT , ADDRESS VARCHAR(500) , ADDRESSTYPE VARCHAR(25));

INSERT INTO CUSTOMER VALUES (100000001,'ADDRESSA' ,'MAILING ADDRESS');
INSERT INTO CUSTOMER VALUES (100000001,'ADDRESSB',  'REGISTERED ADDRESS');
INSERT INTO CUSTOMER VALUES (100000001, NULL ,'OFFICE ADDRESS');

SELECT 
    ISNULL(
        (SELECT ADDRESS FROM CUSTOMER WHERE CUSTOMERID = 100000001 AND ADDRESSTYPE = 'OFFICE ADDRESS' ) ,
        (SELECT ADDRESS FROM CUSTOMER WHERE CUSTOMERID = 100000001 AND ADDRESSTYPE = 'REGISTERED ADDRESS' )
    )

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

use TOP 1 with ORDER BY with condition

select top 1 *
from   address
where  CustomerID = @CustomerID
and    Address is not null
order by case when AddressType = 'Office' then 1
              when AddressType = 'Registered' then 2
              else 3
              end

Upvotes: 1

Related Questions