I've got a query three temp tables.
- Table one Returns PID When PC = 6 and not 7
- Table two Returns PID When PC = 1 and not 7
- Table three Returns PID and not 7
The first two temp tables have a perfect inner join and return only PID that has both PC = 6 and PC = 1, but not when it equals 7.
But, what I want is the third part, to right outer join on PC, and return all the PID's that contain either a PC = 6 or a PC = 1 that is NOT in the inner join. Instead, the right outer join returns the same values. Can someone give me a hand?
--CREATE TEMP TABLE TO PULL PIDs w PLCs but no True Owner--
SELECT
Distinct P.PropertyID as PID
,PC.LocationID AS PLC
INTO #TempPLC
FROM
Property AS P
left join
PropertyContact As PC on PC.PropertyID = P.PropertyID
WHERE
P.PropertyID=PC.PropertyID
and
PC.ContactRoleID = 6
and not exists
(select 1
from
Enterprise.dbo.PropertyContact
where
PropertyID=P.PropertyID
and
ContactRoleID=7)
--CREATE TEMP TABLE TO PULL PIDs w Recorded but no True Owner--
USE ENTERPRISE--
SELECT
Distinct P.PropertyID as PID
,PC.LocationID AS Recorded
INTO #TempRecorded
FROM
Property AS P
left join
PropertyContact As PC on PC.PropertyID = P.PropertyID
WHERE
P.PropertyID=PC.PropertyID
and
PC.ContactRoleID = 3
(select 1
from
Enterprise.dbo.PropertyContact
where
PropertyID=P.PropertyID
and
ContactRoleID=7)
--CREATE TEMP TABLE TO PULL PIDs w PLCs but no True Owner--
SELECT
P.PropertyID as PID
,PC.LocationID AS RemainingLocs
INTO #TempRemaining
FROM
Property AS P
left join
PropertyContact As PC on PC.PropertyID = P.PropertyID
WHERE
P.PropertyID=PC.PropertyID
and not exists
(select 1
from
Enterprise.dbo.PropertyContact
where
PropertyID=P.PropertyID
and
ContactRoleID=7)
--RETURN RESULTS OF ALL PIDS w. BOTH RECORDED AND TRUE OWNERS--
SELECT
Distinct #TempRemaining.PID
,#TempRemaining.RemainingLocs
,#TempRecorded.Recorded
FROM
#TempPLC
inner join
#TempRecorded on #TempRecorded.PID = #TempPLC.PID
right join
#TempRemaining on #TempRecorded.Recorded = #TempRemaining.RemainingLocs
WHERE
#TempRecorded.Recorded = #TempPLC.PLC
--DROP TEMP TABLES--
drop table #TempPLC
drop table #TempRecorded
drop table #TempRemaining