Let's say I have some customers with multiple phone numbers and some with only one number. Anytime there is more than one number, there will always be a type column set equal to 'MAIN' next to one of the numbers. But if there is not multiple numbers then Type can be equal to anything at all. In T-SQL, how do I pick the number tagged as 'MAIN' in those cases when there is more than one number but simply select the only number available in those cases where there are not multiple numbers. Thanks!
Using a subquery on a join would be ideal.. but I can't seem to get it.
CustomerTable:
| ID | Name |
| --- | ----- |
| ID | ACME Inc |
| 1 | Foo Bar |
PhoneTable:
| CustID | Type | Phone |
|---|---|---|
| 1 | blah | 12345 |
| 2 | NULL | 33333 |
| 2 | MAIN | 98765 |
Desired Output:
1, 12345
2, 98765
CodePudding user response：
Join to the phone table twice - first for the MAIN phone, and again for any phone, and use coalesce() to get the first (ie non-null) hit:
select
c.id,
max(coalesce(p.phone, p2.phone)) as phone_number
from customer c
left join phone_table p on p.cust_id = c.id
and p.type = 'MAIN'
left join phone_table p2 on p2.cust_id = c.id
group by c.id
max() is used to return one row only in case there are many non-main phone numbers. If you want them all, remove max and the group by
Note that the condition p.type = 'MAIN' must be in the join condition (not in the where clause) for this to work.
If you put the condition in the where clause, it will force the join to p to be an inner join and customers without a MAIN phone will not be returned.