Check for duplicate data from called functions

Question

I'm new to SQL and am not sure how to go about getting the result set I am after. Below is the closest I have gotten to my desired result set although I am seeing duplicate data.

I have a stored procedure

Select 
o.cFirstName+' '+o.cLastName [owner], o.nContactUID [oUID], 
p.cFirstName+' '+p.cLastName [Process], p.nContactUID [pUID]
from (
 SELECT
 cFirstName, 
 cLastName, 
 nContactUID
 FROM dbo.ip_contact_owner(@facilityID) 
 union 
 SELECT
 cFirstName, 
 cLastName, 
 nContactUID
 FROM
 dbo.ip_contact_owner_alternates(@facilityID)
 ) as o, 
 (
 SELECT
 cFirstName, 
 cLastName, 
 nContactUID
 FROM dbo.ip_contact_process(@facilityID) 
 union 
 SELECT
 cFirstName, 
 cLastName, 
 nContactUID
 FROM
 dbo.ip_contact_process_alternates(@facilityID)
 ) as p

is calling the below table-valued functions for each owner, owner alternate, process and process alternate:

ALTER FUNCTION [dbo].[ip_contact_owner](@facility varchar(100))
RETURNS TABLE
AS
 RETURN (
 SELECT 
 f.nFacilityUID,
 c.cFirstName, 
 c.cLastName,
 c.nContactUID
 from dbo.ip_ContactAssignments as a 
 join dbo.Contacts as c on a.nContactUID = c.nContactUID 
 join dbo.Facilities as f on a.nFacilityUID = f.nFacilityUID
 join dbo.ip_ContactPermissions as p on a.nPermissionUID = p.nPermissionUID
 where 
 a.lActive = -1 and
 c.lActive = -1 and
 f.lActive = -1 and 
 a.lPrimary=-1 and
 p.cDescription = 'owner' and
 f.nFacilityUID = @facility
 )
ALTER FUNCTION [dbo].[ip_contact_owner_alternates](@facility varchar(100))
RETURNS TABLE
AS
 RETURN (
 SELECT 
 f.nFacilityUID,
 c.cFirstName, 
 c.cLastName,
 c.nContactUID
 from dbo.ip_ContactAssignments as a 
 join dbo.Contacts as c on a.nContactUID = c.nContactUID 
 join dbo.Facilities as f on a.nFacilityUID = f.nFacilityUID
 join dbo.ip_ContactPermissions as p on a.nPermissionUID = p.nPermissionUID
 where 
 a.lActive = -1 and
 c.lActive = -1 and
 f.lActive = -1 and 
 a.lPrimary = 0 and
 p.cDescription = 'owner' and
 f.nFacilityUID = @facility
 )

My result set currently looks like this (owner, ownerID, process, processID):

Lisa Simpson |348392000000267 | Bart Simpson | 348392000000347 
Lisa Simpson |348392000000267 | Homer Simpson | 348392000000350 
Lisa Simpson |348392000000267 | Maggie Simpson | 348392000000306 

I would like for it to return like this

Lisa Simpson |348392000000267 | Bart Simpson | 348392000000347 
NULL |NULL | Homer Simpson | 348392000000350 
NULL |NULL | Maggie Simpson | 348392000000306 

Basically, there will always be 1 owner and 1 process name returned but 0 to many owner and process alternates. How do I eliminate the duplicate data?

I'm on SQL Server 2008

• Stijn Wynants: can you provide table design and some sample data?

Answer 1

Right now your code is doing a cartesian join between derived tables o and p. If derived table o contains M rows and derived table p contains N rows then your result set will M x N rows with none of the column values being NULL unless your TVFs return NULL rows. You need to add a join clause between o and p to get the result set that you want.

• Crystal: Thanks Joe - Your explanation helped me understand what I was doing wrong and led me to the solution. I appreciate your time!