0

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

  • 1
    can you provide table design and some sample data? – Stijn Wynants Nov 16 '16 at 11:51
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.

  • Thanks Joe - Your explanation helped me understand what I was doing wrong and led me to the solution. I appreciate your time! – Crystal Nov 17 '16 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.