CODEWITHSUNDEEP
sql-server-2005
user278618
user278618

Reputation: 20262

Simple where in sql

I have a tables X and Y

Table X

a
b
c
d

Table Y

a 1   1
a 32  5
b 1   1
b 4   5
c 5   65

I want in output c and d, because they haven't in table Y record with value 1 1

How can I get it?

Upvotes: 0

Views: 70

Answers (3)

Vishal
Vishal

Reputation: 12369

SELECT col1
FROM TableX
WHERE col1 
NOT IN (select col1
FROM TableY
WHERE col2=1 and col3=1)

Upvotes: 1

Matthew
Matthew

Reputation: 10444

I think I understand:

SELECT X.column FROM X 
WHERE X.column NOT IN
(SELECT Y.column FROM Y WHERE Y.column2=1 AND Y.column3=1)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453563

You don't give any column names so I made some up!

SELECT col1
FROM tablex
EXCEPT
select col1
FROM tabley
WHERE col2=1 and col3=1

If you have oversimplified your example and actually need additional columns from tablex use NOT EXISTS

SELECT tablex.*
FROM   tablex
WHERE  NOT EXISTS (SELECT *
                   FROM   tabley
                   WHERE  col2 = 1
                          AND col3 = 1
                          AND tablex.col1 = tabley.col1)

Upvotes: 3

Related Questions