MySQL LEFT JOIN where multiple values

Question

I have query that joins Products table on Product_Attributes table. I' would like to get products which meat exact condition ex.

Products which have attribute id 9 AND 16 selected, but when I run this query I'll get only product which meets first ID

WHERE ("AttributeValueID" = 9 AND "AttributeValueID" = 16)

I need that products to have both attributes selected AttributeValueID = 9 AND AttributeValueID = 9 not only this or that attribute ID. Using OR / IN is'not solution for me.

Update:

below is a part of query with join / where:

LEFT JOIN "Product_Attributes" ON "Product_Attributes"."ProductID" = "SiteTree_Live"."ID" WHERE ("PriceTax" >= '100' AND "PriceTax" <= '200') AND ("AttributeValueID" IN ('9','8','7')) AND ("AttributeValueID" IN ('5','4'))

thank you in advance for your help

(It's not clear which table the columns in the WHERE clause refer to; because the column references aren't qualified with a table name or table alias. Is PriceTax a column from Product_Attributes or from SiteTree_Live?) — spencer7593
– spencer7593, Commented Apr 21, 2014 at 17:58

Gordon Linoff · Accepted Answer · 2014-04-21 16:34:10Z

4

This is an example of a "set-within-sets" query. I like to solve these using group by and having, because that is a very flexible approach:

select pa.ProductId
from ProductAttributes pa
group by pa.ProductId
having sum(AttributeValueId = 9) > 0 and
       sum(AttributeValueId = 16) > 0;

answered Apr 21, 2014 at 16:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

spencer7593 Over a year ago

+1. To get a JOIN to the products table, one option is to use a query like this one as an inline view (i.e. wrap this query in parens and assign it an alias, and reference that whole thing in the FROM/JOIN clause like it was a table.

spencer7593 · Accepted Answer · 2014-04-21 18:04:26Z

Gordon's answer showed the most flexible way to get the list of ProductId values that meet the specified criteria.

You could use a query like Gordon's (that gets the set of ProductId) as an inline view, and do a JOIN operation against the products table, like this:

SELECT p.ProductID
     , p.ProductName
     , ...
  FROM products p
  JOIN ( 
         SELECT a.ProductID
           FROM ProductAttributes a
          GROUP BY a.ProductId
         HAVING SUM(a.AttributeValueID=9) > 0
            AND SUM(a.AttributeValueID=16) > 0
       ) s
     ON s.ProductID = p.ProductID

UPDATE

based on the edit to the question adding a snippet of SQL text:

JOIN ( SELECT a.ProductID
         FROM Product_Attributes a
        HAVING SUM(a.AttributeValueID IN ('9','8','7')) > 0
           AND SUM(a.AttributeValueID IN ('5','4')) > 0
         GROUP BY a.ProductID
     ) s
   ON s.ProductID = SiteTree_Live.ID
WHERE SiteTree_Live.PriceTax >= '100'
  AND SiteTree_Live.PriceTax <= '200'

Collectives™ on Stack Overflow

MySQL LEFT JOIN where multiple values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related