CODEWITHSUNDEEP
sqlsql-servert-sql
sysboard
sysboard

Reputation: 287

TSQL - Query for multiple types in multiple rows

I have a table where each address has different types, for each type a row. How to find the addresses where for the exact types i need?

Eg.

ID  TypID   Street
1   1       Street 1
1   2       Street 1
2   2       Street 2
3   1       Street 3
3   2       Street 3

In the above i need to find addresses which has type 1 and 2. That query result should be adresses with id 1 and 3.

Upvotes: 0

Views: 58

Answers (3)

Surendra
Surendra

Reputation: 721

This can be done with a inner join like the below 

select y2.*
from <your_table> Y1
JOIN <your_table> Y2
ON   Y1.ID = Y2.ID
AND  Y1.Type_id in (1,2)
AND  Y2.ID in (1,3)

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

You can use INTERSECT for this

select id
from tbl
where typid = 1
intersect
select id
from tbl
where typid = 2

although it won't work in mysql if that happens to be the database you're using.

Upvotes: 1

juergen d
juergen d

Reputation: 204854

Group by the id and then count the different typeids in the having clause

select id from your_table
where typeid in (1,2)
group by id
having count(distinct typeid) = 2

Upvotes: 1

Related Questions