CODEWITHSUNDEEP
sqlsql-server
Marcus Ohlsson
Marcus Ohlsson

Reputation: 267

SQL Join where two parameters must be in the table

I'm using MS SQL Server 2014, and I got one table looking like this: (table1)

PropertyId
1 
2
3
4

And another table looking like this: (table2)

PropertyId   Filter
1            1
1            2
2            1
3            1
4            2

I want to get all properties from the first table having filter 1 AND 2 (in this case only PropertyId 1)

I can do this using:

Select * from table1 where id in (select PropertyId from table2 where filter = 1) AND (select PropertyId from table2 where filter = 2)

If I use join, I will get all properties that's in table2:

Select * from table1 join table2 on table1.PropertyId = table2.PropertyId where table2.Filter in (1,2)

Table1 got about 200'000 rows and table2 about 2'500'000 rows, so the solution must be really effective :)

EDIT: Sorry for being so unclear in my question:

Upvotes: 0

Views: 70

Answers (3)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

See if this works for you:

select PropertyId
from table1
where PropertyId in
(
select t21.PropertyId
from table2 t21
inner join table2 t22 on t22.PropertyId = t21.PropertyId and t22.Filter = 2
where t21.Filter = 1
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One method uses aggregation. If you don't need to filter by the first table, you can simply do:

select propertyId
from table2
where filter in (1, 2)
group by propertyId
having count(*) = 2;  -- "2" = number of things you are looking for

Note: This assumes the property/filter values are unique in the second table. Otherwise, you can use count(distinct).

EDIT:

If you need to filter by the first table, just add it in:

select t2.propertyId
from table1 t1 join
     table2 t2
     on t2.propertyId = t1.propertyId
where filter in (1, 2)
group by t2.propertyId
having count(*) = 2;  -- "2" = number of things you are looking for

Upvotes: 2

Sayed M. Idrees
Sayed M. Idrees

Reputation: 1408

Use inner Join to only get the Connected Keys

Select * from table1 inner join table2 on table1.PropertyId = table2.PropertyId where table2.Filter in (1,2)

Upvotes: 0

Related Questions