help writing this subquery

Question

help writing this subquery

rash3554

SSCommitted

Points: 1984
More actions
November 25, 2014 at 9:55 am

#313401

Hi there,
I have a 2 tables Employee and employee address
Create table employee(eid int, edept varchar(20))
insert into employee(1,'sales')
insert into employee(2,'Marketing')
insert into employee(3,'IT')
insert into employee(4,'Corporate')
Create Table EmpAddress(Eid int, Addresscode int, AddressType varchar(20), Address varchar(200))
insert into EmpAddress(1,10,'Main','2 Elm St,Salem MA')
insert into EmpAddress(2,20,'Main','2 Cider St,Salem MA')
insert into EmpAddress(2,20,'Secondary','2 Willow Rd,Salem MA')
insert into EmpAddress(3,550,'Secondary','22 Estabrook St,Salem MA')
insert into EmpAddress(4,40,'Main','2 Sylvan St,Salem MA')
insert into EmpAddress(4,55,'Main','2 Pinebrook St,Salem MA')
I want for Eid=1, Record #1; for Eid=2, Record #3, for Eid=3,Record #4, for Eid=4, Record #5.
i.e. Rules: If an employee has one Main & one Secondary address pick Main; If there are multiple Main Address,
pick the one with minimum Address code; If there is only Secondary address pick that up.If there are multiple secondary and no Main, pick the one with minimum address code.
I am really not sure how to do this all in one query.
Thanks
R

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

WITH MainAddr AS (

SELECT Eid, Addresscode, Address, ROW_NUMBER() OVER(PARTITION BY Eid ORDER BY AddressCode ASC) AS ROW_NUM FROM EmpAddress WHERE AddressType = 'Main'),

SecAddr AS(

SELECT Eid, Addresscode, Address, ROW_NUMBER() OVER(PARTITION BY Eid ORDER BY AddressCode ASC) AS ROW_NUM FROM EmpAddress WHERE AddressType = 'Secondary'

)

SELECT employee.eid, employee.edept, CASE WHEN MainAddr.Address IS NOT NULL THEN MainAddr.Address ELSE SecAddr.Address END FROM employee

LEFT OUTER JOIN MainAddr ON employee.eid = MainAddr.Eid AND MainAddr.ROW_NUM = 1

LEFT OUTER JOIN SecAddr ON employee.eid = SecAddr.Eid AND SecAddr.ROW_NUM = 1

ORDER BY employee.eid

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

There's no need for 2 CTEs.

WITH cteEmpaddress AS (

SELECT Eid,

Addresscode,

Address,

ROW_NUMBER( )OVER( PARTITION BY Eid ORDER BY Addresstype, Addresscode)AS Row_Num

FROM Empaddress

)

SELECT e.Eid,

e.Edept,

a.Address

FROM Employee e

LEFT OUTER JOIN cteEmpaddress a ON e.Eid = a.Eid

AND a.Row_Num = 1

ORDER BY e.Eid;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

An alternative to Luis' method:

SELECT

e.*,

x.Addresscode, x.AddressType, x.[Address]

FROM #employee e

OUTER APPLY (

SELECT TOP 1 *

FROM #EmpAddress a

WHERE a.Eid = e.Eid

ORDER BY AddressType, Addresscode

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden