Database query: join tags with exact matches or at least one Given I have a query like:

SELECT count(mm.id) AS c
FROM merchant_mobile mm
INNER JOIN mobile m ON mm.mobile_id = m.id
LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id)
INNER JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
INNER JOIN tag t ON t.id = mmt.tag_id
WHERE mm.merchant_id = ?
  AND mm.status LIKE ?
  AND (mm.last_promo_sms_date &lt;= ?
       OR mm.last_promo_sms_date IS NULL)
  AND t.name = ?
  AND t.name = ?
ORDER BY mm.last_promo_sms_date ASC


I need to perform queries to select:


Matches for exact tag combination: eg: pretty AND blonde
Matches for any combination, but at least one of them: pretty OR blonde


Is it possible to make with just one query?
Do I need a subquery?
Is my schema ok?
This is how I solved it:


For exact combination

SELECT count(mm.id) AS c
FROM merchant_mobile mm
INNER JOIN mobile m ON mm.mobile_id = m.id
LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id)
INNER JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
INNER JOIN tag t ON t.id = mmt.tag_id
WHERE mm.merchant_id = ?
  AND mm.status LIKE ?
  AND (mm.last_promo_sms_date &lt;= ?
       OR mm.last_promo_sms_date IS NULL)
  AND mm.id IN
    (SELECT mm.id
     FROM merchant_mobile_customer mmc
     JOIN merchant_mobile mm ON mm.id = mmc.merchant_mobile_id
     JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
     RIGHT JOIN tag t ON mmt.tag_id = t.id
     WHERE t.name = ?
       OR t.name = ?
       OR t.name = ?
       AND mm.merchant_id = ?
     GROUP BY mm.id
     HAVING COUNT(*) = 3 )
ORDER BY mm.last_promo_sms_date ASC ; 

For at least one

SELECT count(mm.id) AS c
FROM merchant_mobile mm
INNER JOIN mobile m ON mm.mobile_id = m.id
LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id)
INNER JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
INNER JOIN tag t ON t.id = mmt.tag_id
WHERE mm.merchant_id = ?
  AND mm.status LIKE ?
  AND (mm.last_promo_sms_date &lt;= ?
       OR mm.last_promo_sms_date IS NULL)
  AND mm.id IN
    ( SELECT mm.id
     FROM merchant_mobile_customer mmc
     JOIN merchant_mobile mm ON mm.id = mmc.merchant_mobile_id
     JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
     RIGHT JOIN tag t ON mmt.tag_id = t.id
     WHERE t.name = ?
       OR t.name = ?
       AND mm.merchant_id = ? )
ORDER BY mm.last_promo_sms_date ASC ;