Film on characteristics of the goods

[Amritpal]

There is a table with product and pole characteristics Good_ID♪ Field_ID♪ Value♪ Maybe a lot of records with one. Good_ID and Field_ID and Value♪

Field_ID - it's the identifier of a certain characteristic.

We need to write a request to make a choice. Good_IDwhich satisfy several couples Field_ID and Value♪ In fact, filtering on the characteristics of the product.

I can't figure out how one request is to pick out all the products that have blue and weight to 1kg.

Updating

Thank you, everyone. I mean, it's 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 didn't make the request correctly, correct the plas.

[yajahira]

You must have a table with a list of goods. Type goods♪ I mean. Good_ID That's it. id from this table.
Next, let's say:

• Your performance table is called attributes♪
• Field_ID for colour = 1
• Field_ID for weight = 2

Then you need a request to look like:

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 request will show you all the supplies from the table. goodswhich have colourField_ID=1blue and weightField_ID=2) = 1.

---

I've got a schematic on my website, so I'm gonna give you some advice: it's better if you make a summary of all the goods and their attributes. Other information can be added: prices, descriptions, names, pictures. ♪ ♪

Something like that:

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 - these are Attribut values 1, 2 and 3 Field_ID)
Depending on the frequency of changes in product information, such a table can be re-engineered once per hour/a few hours/day.

Having such a table, it would be much easier for you to get the data out of the base, make the reports, you can make a quick search on the website (and ask any difficulty will always be in one table)... a lot more. ♪ ♪

I suggest that.