Sql-server – Check for duplicate data from called functions

duplicationset-returning-functionssql server

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

Best Answer

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.

Related Question