Finding multiple attributes in a MySQL query (with a working example)
I have the following tables
table: products
+------------+--------------------+
| product_id | name |
+------------+--------------------+
| 1 | samsung galaxy s8 |
| 2 | apple iphone 7 |
+------------+--------------------+
table: attributes
+--------------+--------------------+
| attribute_id | name |
+--------------+--------------------+
| 1 | brand |
| 2 | color |
+--------------+--------------------+
table: value_attribute
+--------------------+--------------+------------+---------------------+
| attribute_value_id | attribute_id | product_id | value |
+--------------------+--------------+------------+---------------------+
| 1 | 1 | 1 | samsung |
| 2 | 2 | 1 | blue |
| 3 | 1 | 2 | apple |
| 4 | 2 | 2 | red |
+--------------------+--------------+------------+---------------------+
And I have the following request:
Request 1 (WORKS!)
SELECT
p.product_id AS product_id,
p.name AS product_name,
v.value AS attribute_value,
a.attribute_id AS attribute_id,
a.name AS attribute_name,
c.name AS attributes_category_name
FROM
products p
LEFT JOIN
attribute_values v USING (product_id)
LEFT JOIN
attributes a USING (attribute_id)
WHERE
p.product_id IN (
SELECT
p.product_id
FROM
products p
LEFT JOIN
attribute_values v USING (product_id)
LEFT JOIN
attributes a USING (attribute_id)
WHERE
(a.name = 'brand' AND (v.value = 'samsung'))
)
Request 2 (DOES NOT WORK!)
SELECT
p.product_id AS product_id,
p.name AS product_name,
v.value AS attribute_value,
a.attribute_id AS attribute_id,
a.name AS attribute_name,
c.name AS attributes_category_name
FROM
products p
LEFT JOIN
attribute_values v USING (product_id)
LEFT JOIN
attributes a USING (attribute_id)
WHERE
p.product_id IN (
SELECT
p.product_id
FROM
products p
LEFT JOIN
attribute_values v USING (product_id)
LEFT JOIN
attributes a USING (attribute_id)
WHERE
(a.name = 'brand' AND (v.value = 'samsung'))
AND (a.name = 'color' AND (v.value = 'blue'))
)
As you can see, the difference between the two queries is in the WHERE-clausule.
Query 1:
--------
p.product_id IN (
SELECT
p.product_id
FROM
products p
LEFT JOIN
attribute_values v USING (product_id)
LEFT JOIN
attributes a USING (attribute_id)
WHERE
(a.name = 'brand' AND (v.value = 'samsung'))
)
Query 2:
--------
p.product_id IN (
SELECT
p.product_id
FROM
products p
LEFT JOIN
attribute_values v USING (product_id)
LEFT JOIN
attributes a USING (attribute_id)
WHERE
(a.name = 'brand' AND (v.value = 'samsung'))
AND (a.name = 'color' AND (v.value = 'blue'))
)
I am looking in query for one only for brand> samsung, in query 2 for brand> samsung AND color> blue.
Does anyone know why my second query won't work?
source to share
I still don't have enough reputation to comment on stackoverflow, but I think table c is never declared, so I delete it. This is how I found the solution to your problem with your code.
SELECT
p.product_id AS product_id,
p.name AS product_name,
v.value AS attribute_value,
a.attribute_id AS attribute_id,
a.name AS attribute_name/*,
c.name AS attributes_category_name*/
FROM
products p
LEFT JOIN
attribute_values v USING (product_id)
LEFT JOIN
attributes a USING (attribute_id)
WHERE
p.product_id IN (
SELECT
p2.product_id
FROM
products p2
LEFT JOIN
attribute_values v USING (product_id)
LEFT JOIN
attributes a USING (attribute_id)
WHERE
a.name = 'brand' AND v.value = 'samsung' and p2.product_id IN (
SELECT
p3.product_id
FROM
products p3
LEFT JOIN
attribute_values v USING (product_id)
LEFT JOIN
attributes a USING (attribute_id)
WHERE a.name = 'color' AND v.value = 'blue'
)
)
so basically you have to create one filter after another first, not at the same time, there is no value_attribute which is blue and samsung. Hope it helps
source to share
Look at your condition WHERE
in the request second
:
WHERE
(a.name = 'brand' AND (v.value = 'samsung'))
AND (a.name = 'color' AND (v.value = 'blue'))
This condition ultimately looks like:
WHERE a.name = 'brand' AND v.value = 'samsung'
AND a.name = 'color' AND v.value = 'blue'
And it will NEVER
be TRUE
for any recording.
source to share