CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Hot Male
Hot Male

Reputation: 13

SQL Server - Selecting columns with two matching other columns

I have a table like so:

columna int
columnb int

Some sample data:

columna columnb
   1       1
   1       2
   1       3
   2       1
   2       2
   2       4
   3       1
   3       3
   3       7
   4       3
   5       2
   5       3

I want to select columna where the same value for columna has both 1 and 3 in columnb.

So my results for the sample above should be:

1
3

I have tried a number of things but can't get what I want, can somebody help me out here.

Upvotes: 1

Views: 32

Answers (2)

Andrey Korneyev
Andrey Korneyev

Reputation: 26886

You can use intersect operator, it returns distinct rows that are output by both input queries.

So first query should be values of columna from records having columnb = 1, second one - values of columna from records having columnb = 3 and then intersect results of both queries.

select columna
from your_table 
where columnb = 1

intersect

select columna
from your_table
where columnb = 3

Upvotes: 1

D Mayuri
D Mayuri

Reputation: 456

SELECT DISTINCT(t1.columnA) 
FROM Table_Name t1
INNER JOIN Table_Name t2 ON t1.columnA = t2.columnB

Upvotes: 0

Related Questions