0

I've got a query three temp tables.

  1. Table one Returns PID When PC = 6 and not 7
  2. Table two Returns PID When PC = 1 and not 7
  3. 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
  • Hard to make head or tails of that. Why are you using temp tables? Anyway I suspect your issue is you have duplicated your On Clause in your where clause. So get rid of the where in the third query. Right Join gives you nulls then your where clause filters them out. – Tony Hopkinson May 08 '14 at 19:48
  • @Tony Hopkinson. I'm still new to sql. Not sure how to pull this without temp tables since i'm looking for PIDs WHEN PC = 6 and 1 but not 7. Could be an easy solution, but I'm not sure what it is. For the "On Clause", do you mean in my final query (query 4) or the 3rd #TempRemaining query? – Uneakbreed May 08 '14 at 20:57

0 Answers0