Question on UNION syntax 1

[KreativeKai]

I'm new to SQL and trying to understand what is most likely a simple question.

I'm trying to use UNION to pull together a result set of Name and Employee Number. Unfortunately there are duplicates in the two tables. The UNION syntax is supposed to weed out duplicates, but I have this situation.

First Name Last Name EmpID
TOM JONES 1111
TOM L JONES 1111

The code below shows what I have. The UNION evidentally looks at all the fields you bring back with the select. I need it to UNION only on the EmpID, but also have the name in the result list. I don't care which table it pulls the name, because either way, the name should be basically alike.

SELECT LastName,FirstName,EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT LastName, FirstName, SocSecNo AS EmpID
FROM table2
ORDER BY LastName ASC,FirstName ASC, EmpID ASC

Any suggestions how to UNION the information while eliminating duplicates based on EmpID only? Any help is appreciated!!

 

[jby1]

This might do what you want:

SELECT LastName,FirstName,EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL 
UNION
SELECT LastName, FirstName, SocSecNo AS EmpID
FROM table2 t2
LEFT JOIN table1 t1 ON t2.SocSecNo = t1.EmpNo
WHERE t1.EmpNo IS NULL
ORDER BY LastName ASC,FirstName ASC, EmpID ASC

This should select all of the records from table1, and then append any records in table2 where there is no EmpNo in table1.

This assumes that if there is a duplication between tables, you prefer to have the FirstName and LastName from table1.

WARNING: I haven't tested this, so it might not work 100%, but I think the idea is right.

 

[KreativeKai]

Well, I’ve posted my question on two message boards and received several suggestions. First of all Thanks for all your feedback and help!!

Below are the two approaches that I’ve received and I’ve tweaked them to get the result set I was looking for. Table one includes everyone on our payroll including terminated employees. Table two is our health insurance table which includes retirees. The emp number will match especially with the retirees. The problem also comes into play when you have retirees come back to work on a contractual basis. Somedays I wish they would just go golfing.

Hopefully my explanation above helps in understanding the data I’m trying to select. Both of the selects below work where we include all the active folks on our payroll (table1), plus all the active health insurance participants (table2). Both code snipplets also weed out the duplicate Retirees and only list them once.

I now have the question; Is one section of code using a stored procedure is more efficient than the other? I’ve tested both selects in my VB.NET project and the second code snipplet is about 50 milliseconds faster. My record count is 1578. Even if the second code snipplet is faster, is it more efficient to use the join? Nobody will really notice 50 milliseconds, so I want to go with the code that will take the least amount of resources from SQL. I used Query analyzer to perform an Estimated Execution Plan and didn’t notice any real differences. Of course I’m new to this, and not sure what I’m looking at.

Any suggestions or feedback is appreciated!! Again, thanks for the great feedback you’ve already given!!

---------- First Code Sniplet ------------

SELECT [table1].LastName, [table1].FirstName, [table1].EmpNo AS EmpID
	FROM table1
	WHERE EmpTermDate IS NULL 
UNION
SELECT [table2].LastName, [table2].FirstName, [table2].SocSecNo AS EmpID
	FROM table2
		LEFT JOIN [table1] ON [table2].SocSecNo = [table1].EmpNo
	WHERE [table1].EmpNo IS NULL
		OR 
			([table1].EmpNo IS NOT NULL
				 AND 
			[table1].EmpTermDate IS NOT NULL)
	ORDER BY LastName ASC, FirstName ASC, EmpID ASC

---------- Second Code Sniplet ------------

SELECT LastName,FirstName,EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL 
UNION
SELECT LastName, FirstName, SocSecNo AS EmpID
FROM table2 
WHERE SocSecNo NOT IN (SELECT EmpNo FROM table1 WHERE EmpTermDate IS NULL)
ORDER BY LastName ASC,FirstName ASC, EmpID ASC