WHERE id_property = 1 AND id_property = 2

Question

Consider:

SELECT id_post FROM post_property WHERE id_property = 1 AND id_property = 2

Sure you're thinking to use IN()

SELECT id_post FROM post_property WHERE id_property IN (1,2)

But that give me 2 rows as result, and I just need to get all the id_post that matched with the 2 properties

Thanks a lot for your time!

Answer 1

id_property IN (1, 2)

is equivalent to

id_property = 1 OR id_property = 2

NOT this:

id_property = 1 AND id_property = 2.

Answer 2

Using the where clause id_property = 1 AND id_property = 2 will never give you any rows because you're asking for all the rows where id_property equals two different things at the same time. Read the query as:

WHERE (id_property = 1 AND id_property = 2)

instead of

WHERE id_property = 1 AND (those rows where) id_property = 2

It sounds like you want to use OR, but this is equivalent to the query using IN that you have shown. If this doesn't return the rows you want, which rows do you want?

Answer 3

Assume your schema is something like

table post_property (id_property (FK), id_post (FK))

What if the id_property is associated with more than one id_post or the two id_properties are associated with different id_post. Then you would need both records and that is what is being returned. If you want to increase the propert_id in the where and extend the question you are asking is "what post ids do I need to look at?" then what you want is:

Select distinct id_post
from post_property
where id_property in (1,2)

Answer 4

SELECT id_post, count(*) as cnt FROM post_property WHERE id_property IN (1,2) group by id_post having cnt = 2