Filtering by product characteristics

Question

There is a table with product characteristics and columns Good_ID , Field_ID , Value . There may be as many records with one Good_ID and different Field_ID and Value .

Field_ID is the identifier of a specific characteristic.

You need to write a query so that Good_ID selected that satisfy several Field_ID and Value pairs. In fact, filtering by the characteristics of the product.

I can not think of how to select from the table all the goods in a table that have a blue color and a weight of up to 1 kg.

Update

Thanks to all. In general, in the end it turned out to be implemented like this

 SELECT DISTINCT Good_ID FROM chars WHERE Message_ID IN (SELECT Good_ID FROM chars WHERE Field_ID = 3 AND Value = 'серый') AND Message_ID IN (SELECT Good_ID FROM chars WHERE Field_ID = 2 AND Value = '3')

If I have not correctly composed the request - correct plz.

@titov_andrei do not use the query , the label does not add meaning to the question.
If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer).

cyadvert cyadvert 6,724 one 12 31 · Accepted Answer · 2015-12-28T04:03:44

You must have a table with a list of products. Type goods . Those. Good_ID is the id from this table.
Next, suppose that:

Your characteristics table is called attributes .
Field_ID for color = 1
Field_ID for weight = 2

Then the request you need will look like this:

 SELECT g.* FROM goods g JOIN attributes aCo AS g.id=aCO.Good_ID AND aCo.Field_ID=1 AND aCo.value='синий' JOIN attributes aW AS g.id=aW.Good_ID AND aW.Field_ID=2 AND aW.value=1

This query will show you all the goods from the goods table, whose color ( Field_ID=1 ) is blue and weight ( Field_ID=2 ) = 1.

I have implemented the scheme described by you on sites, so let me give you advice: it would be better if you make a summary table of all products and their attributes. You can add other information there: prices, descriptions, titles, pictures ...

Something like this:

 Good_ID SKU title price a1 a2 a3 ------- --------- --------------------- ------ --- --- --- 2 [артикул] [наименование товара] 450.00 red 3 45 452 [артикул2] [наименование товара 2] 15.00 white 5 64

a1 , a2 , a3 are the values of attributes 1, 2 and 3 (you have Field_ID ).
Depending on how often information about the product changes, such a table can be re-generated once per hour / several hours / once a day.

Having such a table, it will be much easier for you to retrieve data from the database, to make reports, you can do a quick (very fast) search on the site (after all, requests of any complexity will always be in one table) ... well, a lot more ....

I strongly advise.

there was also a need to filter in this way, the join worked slowly, did it with the help of the union , i.e.
select необходимые поля from table where условие union следующее условие and the subsequent grouping.
Unfortunately, it’s not possible to change the table, since the project is a combat one and the task is only to get information from the existing table.
It is interesting, when it will be immediately filtered according to 20 characteristics, will not join slow down?
perhaps join with (select 1,'синий' from DUAL union select 2,'1' from DUAL) and count the number of matches for each id (generally similar to the idea from the second answer) will be faster, or maybe not.
And the author of this answer proposed not to change the structure of the existing table, but to make a cache table that should be formed from time to time.
@ Bald56rus request with UNION for some reason gives Good_ID so if in a normal request just put OR that is
and blue color and weight 1kg in the results, but also black color and weight 1 kg for example
And if you need to do a LEFT JOIN , it will be even worse.
As quickly, with or without UNION you will have to check it yourself - it depends on the filtering data, the size of the table.

Nick Volynkin ♦ 24.6k 14 94 175 · Answer 2 · 2015-12-28T03:15:34

Something like this, you can try:

 SELECT Good_ID FROM table WHERE (Field_ID in(идентификатор цвета, идентификатор веса) AND (Value=параметр отвечающий за синий or Value<=1)) GROUP BY Good_ID HAVING Count(*)=2

Filtering by product characteristics

2 answers 2

More articles: