Still confused by having and subqueries in MySQL

Question

I'm trying to select products based on facets for a product category page.

The basic structure is:

Product_Facets
--------------------------------
UID      ProductID       FacetID       FacetOptionID
 1           1              1               1
 2           1              1               2
 3           1              2               4
 4           1              2               7

Products
--------------------------------
ProductID      ProductName
    1           Some Widget

I want to select all products which have a facet record set to the correct value for ALL of the user selected facets.

So if I have a request for:
Facet ID 1 set to value 6 AND
Facet ID 2 set to value 97 AND
Facet ID 5 set to value 43 AND

I want the query to get all products from the products table that have ALL of those facet records in the facets table for any given product. The query should not return products that only meet some of the requirements.

I think I need to do a sub-query inside of a having clause but I'm not sure how that gets structured?

Do you mean Facet ID 1 has FacetOptionID 6 AND Facet ID 2 has FacetOptionID 97 AND Facet ID 5 has FacetOptionID ? — CristiC, Jun 07 '11 at 15:00

Fosco · Accepted Answer · 2011-06-07T15:11:59.327

One method would be using EXISTS clauses, which you could generate dynamically based on the request:

select p.*
from Products p 
where 
   exists (select 1 from Product_Facets where ProductID = p.ProductID
                                        and FacetID = 1
                                        and FacetOptionID= 6)
and
   exists (select 1 from Product_Facets where ProductID = p.ProductID
                                        and FacetID = 2
                                        and FacetOptionID= 97)
and
   exists (select 1 from Product_Facets where ProductID = p.ProductID
                                        and FacetID = 3
                                        and FacetOptionID = 43)

Another method would be straight inner joins (also easily generated dynamically):

select p.*
from Products p
join Product_Facets f1 on p.ProductID = f1.ProductID
         and f1.FacetID = 1 and f1.FacetOptionID = 6
join Product_Facets f2 on p.ProductID = f2.ProductID
         and f2.FacetID = 2 and f2.FacetOptionID = 97
join Product_Facets f3 on p.ProductID = f3.ProductID
         and f3.FacetID = 3 and f3.FacetOptionID = 43

Either method will only return records from Products where Product_Facets records exist for each requested FacetID and FacetOptionID (I assume this is the Value field you mentioned.)

score 0 · Answer 2 · answered Jun 07 '11 at 15:38

I would prequery from the facets table ONLY for those entities that match and apply HAVING count exactly equal to the criteria you've opted for, then join that to the products table.

The first "PreQuery" applies an "OR" to each combination as testing each row individually... yet the HAVING clause ensures that ALL 3 criteria WERE properly qualified.

SELECT STRAIGHT_JOIN
      P.*
   FROM
      ( select pf.ProductID,
               count(*) as MatchedCriteria
           from
              Product_Facets pf
           where
                 (pf.FacetID = 1 AND FacetOptionID = 6 )
              OR (pf.FacetID = 2 AND FacetOptionID = 97 )
              OR (pf.FacetID = 5 AND FacetOptionID = 43 )
           group by
              pf.ProductID
           having
              count(*) = 3 ) PreQuery

      Join Products p
         on PreQuery.ProductID = p.ProductID
   order by
      p.ProductName

Still confused by having and subqueries in MySQL

2 Answers2