• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

XOR Select

Hi everyone,

need help.

I have two table with same structure (T1 and T2).
T2 is a subset of T1.

How can I select from T1 such that I can exclude the rows found in T2.

e.g. T1 has 467 rows, T2 has 72 rows.  I want to get the 395 rows

is there something like a
SELECT * FROM T1 NOT IN T2

Thanks in advance
0
3Mann
Asked:
3Mann
  • 2
1 Solution
 
BillAn1Commented:
there are various equivalent forms, try this one, it is normally the most efficient :

SELECT T1.* FROM T1 left outer join T2 on T1.PrimaryKey = T2.PrimaryKey WHERE t2.PrimaryKey IS NULL
0
 
3MannAuthor Commented:
there is no primary key for the table
just a compund key
table has 3 columns and the 3 columns are the members of the compund key

is it still possible?
0
 
Renante EnteraCommented:
Hi 3Mann!

Perhaps, this is what you are looking for :

Select t1.* From t1
Where Not Exists
(
  Select * From t2
  Where t2.field1 = t1.field1
  And t2.field2 = t1.field2
  And t2.field3 = t1.field3
)

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
BillAn1Commented:
you can use compound key just the same :

SELECT T1.* FROM T1 left outer join T2
on T1.field1 = T2.field1
and T1.field2 = T2.field2
and T1.field3 = T2.field3
WHERE t2.PrimaryKey IS NULL
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now