Buda Gavril Buda Gavril - 25 days ago 8
SQL Question

SQL Server : retrieve records for a foreign key that don't have common values with another foreign key in the same table

I have a table with the following structure:

Id MemberId Field1 Field2 Data
--------------------------------------------------
1 1 12 abc 1232
2 2 13 asl 234
3 2 12 abc 2345
4 1 3 sd sfsd
5 1 5 45r ffgf


Given parameters
member1
and
member2
, I have to return all id's from
member1
that don't have the same values in
Field1
and
Field2
with
member2
.

Output example:

member1 = 1
member2 = 2


Expected output:

4
5


Because the the first record and the third record have the same values for
Field1
and
Field2
.

How to achieve this?

I'm using SQL Server 2014.

Edit: I am not allowed to use cursors and temp tables (like
#tempTable
), I can use only table variables

Answer

If I understand correctly, you can use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.member1 = t.member1 and
                        t2.id <> t.id and
                        (t2.field1 = t.field1 or t2.field2 = t.field2)
                  );
Comments