CODEWITHSUNDEEP
sql
RRM
RRM

Reputation: 3451

SQL join - join first no zero element if possible

I have a data structure something like this:

Company:
ID Name
1 A
2 B
3 C
4 D
5 E

Contact
ID CompanyID Phone
1  1         (12)111
2  1         (12)222
3  2         NULL
4  2         (12)333
5  3         NULL
6  5         (12)444

I need to match company with its phone - one record for company. In 70% of cases in my data a company has one contact with one phone and this is easy. But sometimes there are more contacts and some phones are null.

So my logic would be: - if company has more than 1 contact take 1st not null phone (by ID) - if it is not possible then phone=null

So results of my query would be

A (12)111
B (12)333
C NULL
D NULL
E (12)444

It's probably easy but I got stuck with it ;) Thanks for any help

Upvotes: 0

Views: 51

Answers (2)

Kiril Rusev
Kiril Rusev

Reputation: 753

Try the following: 

SELECT com.name
,MIN(case when isnull(con.phone, 'null') != 'null' then con.phone end) a

FROM Company com
LEFT JOIN Contact con on com.id=con.companyid
GROUP BY com.name

Upvotes: 1

Rich
Rich

Reputation: 865

select a.[Name], min(b.[Phone])
from Company a
JOIN Contact b
 on a.ID = b.CompanyID

then, optionals, e.g.

WHERE b.[Phone] is not null

then group

GROUP BY a.[Name]

Upvotes: 1

Related Questions