Find common value from a column where value from separate column matches 3 different values

Apologies for the poorly worded title. I was not entirely sure how to describe the issue. It’s easier illustrated too me.

I have a database table that lists content ids and values. One ID can have different values so id = 1 may have 1 or many rows depending on how many values are associated with it.

I want to find all ids that are associated with 3 different values in this case. Currently, the SQL is written in several subqueries but this causes a great deal of time to resolve if there are a lot of different values being searched for.

example:

Select id from table1 where type = 'report'
and id in (select id from table2 where value=1)
and id in (select id from table2 where value=2)
and id in (select id from table2 where value=3) 
order by id

The content can have many linked tables to do subquery searches on. The more added the more run time which is not ideal. What is the best method to reducing the number of subqueries?

Answer

you could use inner join with table2 using different table name alias

Select t1.id 
from table1  t1
where t1.type = 'report'
INNER JOIN table2 t2a ON t1.id = t2a.id  and t2a.value  = 1
INNER JOIN table2 t2b ON t1.id = t2b.id  and t2b.value  = 2
INNER JOIN table2 t2c ON t1.id = t2c.id  and t2c.value  = 3 
order by t1.id

and for better performance you should add a composite index on table2 on column (value, id) and on table table1 on columns ( type, id)