MySQL Question

sql multiple where on join

A have a table called

advert_property


pic1

And I have a table
advert
, which is not important, advert properties connects to advert by
advert_id
column in
advert_property
table.

I wrote this SQL request :

SELECT *
FROM `advert`
JOIN advert_property ON advert.id = advert_property.advert_id
WHERE (advert_property.property_id = 1
AND advert_property.property_value = "Манчего")
AND (advert_property.property_id = 2
AND advert_property.property_value = "козий")


What I want to get, is advert that have certain properties, for example : I want an idvert that have property_id = 1 and property_value = "Манчего" AND have property_id = 2 and property_value = "козий". SQL request returns null, how should I change my SQL request. Thanks!

Answer

Assuming I'm understanding your question correctly and you want to return all adverts that have both properties, then there are a couple ways of doing this using multiple joins, exists, in, group by...

Here is the method using multiple joins:

SELECT a.* 
FROM `advert` a 
    JOIN advert_property ap ON a.id=ap.advert_id 
        AND ap.property_id = 1 AND ap.property_value = 'Манчего'
    JOIN advert_property ap2 ON a.id=ap2.advert_id 
        AND ap2.property_id = 2 AND ap2.property_value = 'козий'