DevWL DevWL - 3 years ago 155
MySQL Question

Checking two columns in each row to mach a set of values

OneToMany relationship



I am not pro with mysql so I gues there is some simple solution for my problem. Basicaly:

how can check each row individualy if it contains a certain values (if two columns in a row contains two custom values).

I know how I coud check the first row thanks to GROUP BY ran on unique index of config__atribute__value table. But what if I want to make another AND to check if some other row also maches different values.

Query abstract representation:



SELECT price FROM ONE LEFT JOIN MANY ON ONE.id= MANY.fk_ONE WHERE (col1="some_value" and col2="some_value") AND (col1="some_value" and col2="some_value") GROUP BY ONE.id

* Both rows have to mach


(ONE) table product_config


id, product_type, inStock, inSale, price
1 "flyers A4" 1 1 800.00



(MANY) table config__atribute__value


id | fk_product_config | fk_product_atribute | fk_product_atribute_value
7 1 "color" "4/0"
8 1 "paper" "120g"


Oryginal query:



enter image description here

The query below works for one row only. But how can I check the next row ?

SELECT price FROM product_config p
LEFT JOIN config__atribute__value c
on p.id = c.fk_product_config
WHERE
(c.fk_product_atribute = "paper" and c.fk_product_atribute_value = "120g")
GROUP BY p.id


This will not match enything
because it is checking each row for all the values.

SELECT price FROM product_config p
LEFT JOIN config__atribute__value c
on p.id = c.fk_product_config
WHERE
(c.fk_product_atribute = "paper" and c.fk_product_atribute_value = "120g")
AND
(c.fk_product_atribute = "color" and c.fk_product_atribute_value = "4/0")
GROUP BY p.id


Idealy if I could use ORDER BY for each row, it would solve my problem, of course this would throw an Error.

SELECT price FROM product_config p
LEFT JOIN config__atribute__value c
on p.id = c.fk_product_config
WHERE
(c.fk_product_atribute = "paper" and c.fk_product_atribute_value = "120g")
GROUP BY p.id
AND
(c.fk_product_atribute = "color" and c.fk_product_atribute_value = "4/0")
GROUP BY p.id


enter image description here

I am adding this so you can better understand what i am trying to do.

Answer Source

Let INNER JOIN do the work for you!

SELECT price FROM product_config p 
INNER JOIN config__atribute__value c1 ON p.id = c1.fk_product_config 
  AND c1.fk_product_atribute = 'paper' 
  AND c1.fk_product_atribute_value = '120g'
INNER JOIN config__atribute__value c2 ON p.id = c2.fk_product_config
  AND c2.fk_product_atribute = 'color' 
  AND c2.fk_product_atribute_value = '4/0'

Because of using inner join, the only product configs returned will be those that have both attributes, and those attributes have the desired values.

This might demonstrate why EAV is a terrible way to structure relational data. Your queries become huge and complicated.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download