Mysql – Database query: join tags with exact matches or at least one

MySQL

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 <= ?
       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:

  1. Matches for exact tag combination: eg: pretty AND blonde
  2. 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?

Database schema

Best Answer

This is how I solved it:

  1. 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 <= ?
       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 ;

  2. 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 <= ?
       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 ;
Related Question