November 15, 2016 at 4:12 pm
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 all
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 nvarchar(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 nvarchar(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?
November 16, 2016 at 9:56 am
Add two columns that are only from the Owner. Then set them to null on the second query in the UNION. That ought to return what you're looking for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 16, 2016 at 2:28 pm
These types of functions are best left to the presentation layer.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 16, 2016 at 2:44 pm
drew.allen (11/16/2016)
These types of functions are best left to the presentation layer.Drew
+100
November 17, 2016 at 3:38 am
I was able to solve my issue by using a full outer join. See solution below:
SELECT
....
from
(SELECT
...
FROM dbo.ip_contact_owner(@facilityID)
union all
(SELECT
...
FROM
dbo.ip_contact_owner_alternates(@facilityID))
) o
full outer join
(SELECT
...
FROM dbo.ip_contact_process(@facilityID)
union all
(SELECT
...
FROM
dbo.ip_contact_process_alternates(@facilityID))
) p ON p.nContactUID = o.nContactUID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply