MySQL query - Select a unique column comparing other column

I've a table with the following structure:

id  | property_id | location_type
1   | 1           | 1
2   | 1           | 2
3   | 2           | 1
4   | 3           | 2开发者_运维问答
5   | 4           | 1
6   | 4           | 2

id - is the primary key of the table. property_id is the property ID of my database (foreign key). location_type is beach (value - 1), mountain (value - 2)

Can you please help me in getting the SQL query to select the property_id with location_type = 1 AND location_type = 2 i.e. a property has beach and mountains.

I have lot of filters (around 9 types of location_type and other filters). I'm creating a property search engine with filters. Please help in getting the most optimized query so load time is less.

select
property_id
from table
where location_type in (1,2)
group by property_id
having count(distinct(location_type)) = 2

if you don't have duplicates you can remove distinct clause.

A self-join would eliminate the need for a subquery, although that doesn't mean that it will be faster; normal profiling rules apply:

SELECT table1.property_id
  FROM table table1
  INNER JOIN table tabel2 ON table1.property_id = table2.property_id
  WHERE table1.location_type = 1
  AND table2.location_type = 2