Mysql Inner Join same table

Question

Table structure for table bp_hotel_items

|id|hotel_id|area_id|item_id|price|
|4 |2       |2      |2      |50.00
|5 |2       |2      |3      |100.00
|6 |2       |2      |4      |100.00
|7 |2       |2      |5      |170.00
|10|2       |2      |6      |70.00
|29|1       |0      |2      |95.00
|30|1       |0      |3      |150.00
|31|1       |0      |4      |300.00
|32|1       |0      |5      |120.00

Above is the table and I used this query

SELECT DISTINCT a.item_id 
FROM bp_hotel_items a 
WHERE a.hotel_id IN (1,2 ) AND a.item_id NOT IN (2,3)"

expected output:

item_id
4
5

but I get this output instead:

item_id
4
5
6

Can anyone help me with the query so that I get output as excepted?

Answer 1

This query should give you the expected result:

SELECT DISTINCT a.item_id 
from bp_hotel_items a 
WHERE a.hotel_id IN (1,2 ) AND a.item_id NOT IN (2,3,6)