I am writing an ecommerce script but have stumbled upon a problem: MySQL can't use WHERE cat_id = 'software' AND attr_id IN (1,2) AND attr_id IN (3,4) as it consider ambiguous.
So, to explain the DB logic it is like this :
attrTitle is the title of the attr. Example: "Operating System", "License Type"
attr holds the attribute related to the attrTitle. Each product can have multiple attr and every attr is linked by an FK to an attrTitle. Example of attr : "Windows", "Linux", "Freeware"
product is... well... products. Example: "Microsoft Office"
and category are categories... Each product is linked to only one category... Example: "Office Suites"
So, i want the user to be able to filter the products in the "Office Suites" category. For that, he have a menu saying:
[FILTERS]
*Operating System
- Windows
- Linux
- Mac
*Language
- English
- Español
So, suppose an user is looking for an Office suite that works in Windows AND is in English or Español.
The query became something like this :
SELECT *
FROM prd_product p0_
INNER JOIN cat_category c2_
ON p0_.cat_id = c2_.cat_id
LEFT JOIN prd_attr p4_
ON p0_.prd_id = p4_.prd_id
LEFT JOIN attr_attributeValue e3_
ON e3_.attr_id = p4_.attr_id
WHERE c2_.cat_friendlyUrl = 'software'
AND e3_.attr_id IN (4)
AND e3_.attr_id IN (10,11)
GROUP BY p0_.prd_id
But, as i said, it becomes ambiguous for MySQL as the espval_id is getting an WHERE IN clause two times, and then it returns an empty result. Could anyone give me a tip on how to solve this ?
And yes, i tested and there is a product with attr 4 and attr 10
EDIT: Sql Fiddle : http://sqlfiddle.com/#!2/be3198/1
No its because the column espval_id is on multiple table. And you are using join
You need to specify tablename explicitly as
tablename.colname IN (4)
AND
tablename.colname IN (10,11)
And why AND instead of that why not
tablename.colname IN (4,10,11)