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.
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
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.
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)
