CODEWITHSUNDEEP
mysql
Avatarus
Avatarus

Reputation: 141

Select values from table with multiple attributes

I have 3 tables: 1: products (id, name) 2: product_attributes (attribute_id, name) 3: product_attributes_selected (product_id, attribute_id,value)

Now I want to get all product_id that have two or more desired attributes and values How can I accomplish that?

I tried this, but it failed:

select p.id,p.nazwa_pl 
from produkty p,produkty_atrybuty_wartosci paw 
where (paw.atrybut_id=2 and paw.wartosc=4) 
  and (paw.atrybut_id=3 and paw.wartosc=0) 
  and p.id=paw.produkt_id 
group by p.id

Upvotes: 0

Views: 7730

Answers (4)

Gianluca Vagnoni
Gianluca Vagnoni

Reputation: 49

Having a table1 resource with columns {'id','name',....} and a table2 resource_attribute with columns {'id','resource_id','attribute_name','attribute_value'} where column resource_id is a foreign key pointing to Resource.id, you can do it in this way:

SELECT r.* FROM resource r LEFT JOIN resource_attribute ra ON r.id = ra.resource_id
AND (
    (ra.NAME = 'height' AND ra.VALUE = '20') #First attribute
    OR
    (ra.NAME = 'color' AND ra.VALUE = 'blue') #Second attribute
)
GROUP BY r.id
HAVING COUNT(*) = 2 #Total number of attributes searched

of course if you want to make it dynamic, and use the attributes name and values you need, you can save the number of attributes you want to use in your query and reuse it in the final COUNT(*) function.

I use this query dynamic in my code (java) and it works.

Upvotes: 1

Avatarus
Avatarus

Reputation: 141

Actualy I found ansfer that works for me

SELECT p.id,p.nazwa_pl FROM produkty p JOIN produkty_atrybuty_wartosci paw ON p.id=paw.produkt_id  WHERE (paw.atrybut_id,paw.wartosc) IN ((2,4),(3,0)) GROUP BY p.id, p.nazwa_pl HAVING COUNT(DISTINCT paw.atrybut_id)=2

Thanks for Your help and time

Upvotes: 0

strauberry
strauberry

Reputation: 4199

With the following query you get all ids. The idea is the following: you get all products that have the attribute 2 OR 3 OR 4. Afterwards, you group the result by the product id and count the grouped items (grouped). Only those entries that group 3 entries (you are searching for 3 attribute ids) are sufficient. Obviously, the resulting products can have more attributes, but you asked for at least the provided attributes.

SELECT p.id, p.nazwa_pl, paw.bibkey, paw.keywordid, COUNT(*) as grouped
FROM produkty p, produkty_atrybuty_wartosci paw
WHERE p.id=paw.produkt_id AND paw.atrybut_id IN (2, 3, 4) GROUP BY p.id
HAVING grouped = 3;

Upvotes: 1

T McKeown
T McKeown

Reputation: 12847

I'n my example we have a table named Attribs, some how you need to create (e.g. TABLE variable, or CTE or some other way), my code will show you how to JOIN it.

CREATE TABLE Attribs( Attrib INT,  Val INT )

^^ Populate some data in it... (again could be automated) ^^

select p.id,p.nazwa_pl 
from produkty p
JOIN produkty_atrybuty_wartosci paw 
  ON  p.id=paw.produkt_id 
JOIN Attribs AS A
  ON A.Attrib = paw.atrybut_id
 AND A.Val = paw.wartosc

Upvotes: 1

Related Questions