SQL query to return difference between table joined to itself

Question

I'm using SQL Server 2012.

I have a view called AdvApp.vPortfolioGroupMemberFlattened I want to return all memberid's that are in portfoliogroupcode master or portfoliogroupcode open but are not in both groups. I don't care about members in other groups.

The following stackoverflow article is close to what I need, but doesn't seem to work when joining a table to itself.

sql query to return differences between two tables.

This is my table.

PortfolioGroupCode  MemberID    MemberCode
master              316         abc
master              317         def
open                316         abc
open                317         def
open                321         ghi
master              322         jkl
closed               89         lmn

This is my desired result showing the delta between members of master and open.

PortfolioGroupCode  MemberID    MemberCode
open                321         ghi
master              322         jkl

I tried the following two queries, both from the aforementioned article.

SELECT A.*, B.*
FROM [AdvApp].[vPortfolioGroupMemberFlattened] a
FULL JOIN [AdvApp].[vPortfolioGroupMemberFlattened] b
ON (A.PortfolioGroupCode = B.PortfolioGroupCode)
WHERE A.PortfolioGroupCode IS NULL OR B.PortfolioGroupCode IS NULL

AND

SELECT DISTINCT a.membercode, a.portfoliogroupcode
FROM [AdvApp].[vPortfolioGroupMemberFlattened] a
WHERE (
NOT EXISTS (
SELECT b.membercode
FROM [AdvApp].[vPortfolioGroupMemberFlattened] b
WHERE a.PortfolioGroupCode = 'master'
AND b.PortfolioGroupCode = 'open'
))

chetan · Answer 1 · 2013-07-03T04:29:25.270

1

select distinct PortfolioGroupCode,  MemberID,    MemberCode 
from test 
where memberID not in (
     select distinct a.memberID from test a, test b
     where a.memberId = b.memberID 
     and a.PortfolioGroupCode in ('open','master')
     and a.PortfolioGroupCode <> b.PortfolioGroupCode) 
and PortfolioGroupCode in ('open','master')

Here is the DEMO

edited Jul 03 '13 at 04:29

answered Jul 03 '13 at 03:49

chetan

2,876
1
14
15

the query analyzer is showing me an error where temp; is. I get this error with or without the semicolon. I can also remove temp; and the query runs butthis is not the correct result. Thanks – Jay C Jul 03 '13 at 04:23
I can get your test to work but for some reason in the actual database the result returned is blank? – Jay C Jul 03 '13 at 04:56
I got it to work, perhaps I needed to mention master is bigger than open. Open is a subset of master. I will include the working query above. – Jay C Jul 03 '13 at 05:09

score 0 · Answer 2 · answered Jul 03 '13 at 05:09

This works note the disctinction with these lines

 and a.PortfolioGroupCode = 'master'
 and b.PortfolioGroupCode = 'open'

working query

select distinct PortfolioGroupCode,  MemberID,    MemberCode 
from AdvApp.vPortfolioGroupMemberFlattened  
where memberID not in (
     select distinct a.memberID from AdvApp.vPortfolioGroupMemberFlattened  a, AdvApp.vPortfolioGroupMemberFlattened  b
     where a.memberId = b.memberID 
     and a.PortfolioGroupCode = 'master'
     and b.PortfolioGroupCode = 'open'
     and a.PortfolioGroupCode <> b.PortfolioGroupCode) 
and PortfolioGroupCode in ('open','master')

SQL query to return difference between table joined to itself

2 Answers2