CODEWITHSUNDEEP
sqlsql-servert-sql
PJW
PJW

Reputation: 5417

SQL statement to return overlap from two Select Statements

I have a table on SQL server and two Select statements with different WHERE criteria

What I want are the results that match both Select Statements, not just one.

I've tried nested SELECT statements but I'm not having much luck.

My table looks like the follow

ID    Name    Field2    Field3

1     John     100       NULL
2     Dave     150       NULL
3     Tim      NULL      150
4     Dave     NULL      150

And my SELECT statements are as follows:

SELECT * FROM table WHERE Field2 = 150
SELECT * FROM table WHERE Field3 = 150

But I want to return just 'Dave' because only he appears in the results for both SELECT statements.

How do I achieve this?

Upvotes: 2

Views: 1539

Answers (3)

Beth
Beth

Reputation: 9617

or just inner join:

select name from (
(SELECT name FROM table WHERE Field2 = 150) f2 inner join
(SELECT name FROM table WHERE Field3 = 150) f3 on 
f2.name = f3.name

Upvotes: 0

Taryn
Taryn

Reputation: 247810

You can use EXISTS:

SELECT t1.name
FROM table1 t1
WHERE (Field2 = 150) 
  and exists (select *
              from table1 t2
              where Field3 = 150
               and t1.name = t2.name);

See SQL Fiddle with Demo

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Use INTERSECT:

SELECT Name FROM table WHERE Field2 = 150
INTERSECT
SELECT Name FROM table WHERE Field3 = 150

Upvotes: 7

Related Questions