sql join with condition

Question

I've a table EMPLOYEE which has columns like these

EmpId FName LName

I have another table ADDRESS which has columns like these

EmpId AddressType Address Phone Email

AddressType column has 2 possible types, Residential and Official and an Emp can have both types of address.
I need a query which will join these 2 tables using EmpId.
It also needs to fetch one address which has phone not null.
If both addresses has phone, then fetch any one, if none has phone, still fetch any one.
Please help.

Please, explain your question further. Kindly, show some sample data. Also, show us what have you done already to solve your problem — Rigel1121
– Rigel1121, Commented Feb 14, 2015 at 3:42

Menno · Accepted Answer · 2015-02-14 07:36:47Z

The trick is to first decide which Address would be best for the Employee, based on your Phone-rule. After the prefered Address has been found, indicated by PhonePreference = 1, you can JOIN the correct Address on the Employee.

WITH AddressCTE AS ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY EmpId ORDER BY CASE WHEN Phone IS NOT NULL THEN 1 ELSE 2 END, Phone ) PhonePreference FROM Address ) SELECT * FROM Employee E JOIN AddressCTE A ON E.EmpId = A.EmpId AND A.PhonePreference = 1

Collectives™ on Stack Overflow

sql join with condition

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related