vlookup in sql and removing duplicates

table A with unique cust id

table B with cust_id and a column dealer/direct

desired output

im trying to vlookup dealer/direct from table B to table A in SQL

Conditions:

if a customer is dealer i have to take dealer
if a customer is direct i have to take direct
if a customer is both dealer and direct i have to take direct only

is there anyway in sql to do this.. i did this in sql using pivots and vlookup

Answer

A simple method is aggregation:

select a.cust_id, a.name, max(b.col)
from a join
     b
     on a.cust_id = b.cust_id
group by a.cust_id, a.name;

This works because 'direct' > 'dealer' (under any reasonable collation), so max() will return 'direct' if any values are direct.