Adam Baranyai Adam Baranyai - 5 months ago 8
MySQL Question

MySQL - change OR to AND

Let's presume I have the following query:

SELECT Ads.AdId
FROM Ads
, AdsAmenities
WHERE AdsAmenities.amenities_AmenityId IN (2, 18, 1)
AND Ads.AdId = AdsAmenities.ads_AdId


This query selects each AdId from the Ads table, which has at least one of the following three amenities (2, 18, 1). The
AdsAmenities
table is the one, where I store the relation between
Ads
and
Amenities
. How could I change this query, that it should return only the AdId's which have all of the enumerated amenity ids?

Answer

You can try with having:

SELECT Ads.AdId 
FROM Ads 
JOIN AdsAmenities ON Ads.AdId = AdsAmenities.ads_AdId 
WHERE AdsAmenities.amenities_AmenityId IN (2, 18, 1) 
GROUP BY Ads.AdId
HAVING COUNT(distinct AdsAmenities.amenities_AmenityId) = 3

In case AdsAmenities.amenities_AmenityId values are uniq, you can skip the distinct part.

Comments