A question of restructuring of basis or correctly written request?

Question

MySQL

There is a table of goods.
There are those parameters that a product can have in the amount of several pieces. For example, the scope.
There was a need to filter the goods. By certain parameters, that is, you need to use 2-3 parameters from the product itself, plus those that are in external tables
. Filtering data is stored in the same tables

in these id_product is the category number id_cut parameters by which to filter.
I sketched the code, but I understand that there are gaps:
1. If at least one category is missing, the conditions will not be met.
in general, I'm a little in a stupor.

SELECT t1.id
FROM
  `oboi_category_stily`,
  `oboi_category_oblasty_primeneniya`,
  `oboi_category_osobennosty`,
  `oboi_category_dlya`,
  `oboi_category_risunok(dizayn)`,
  `oboi_tovar_stily`,
  `oboi_tovar_oblasty_primeneniya`,
  `oboi_tovar_osobennosty`,
  `oboi_tovar_oboi_dlya`,
  `oboi_tovar_risunok(dizayn)`,
  oboi_tovar as t1
WHERE
  t1.material IN (SELECT id_cut
                  FROM `oboi_category_material`
                  WHERE id_product = 1) AND
  t1.tip IN (SELECT id_cut
             FROM `oboi_category_tip`
             WHERE id_product = 1) AND
  t1.vid_izdeliya IN (SELECT id_cut
                      FROM `oboi_category_vid_izdeliya`
                      WHERE id_product = 1) AND

  `oboi_category_stily`.id_cut = `oboi_tovar_stily`.id_cut AND
  `oboi_category_oblasty_primeneniya`.id_cut = `oboi_tovar_oblasty_primeneniya`.id_cut AND
  `oboi_category_osobennosty`.id_cut = `oboi_tovar_osobennosty`.id_cut AND
  `oboi_category_dlya`.id_cut = `oboi_tovar_oboi_dlya`.id_cut AND
  `oboi_category_risunok(dizayn)`.id_cut = `oboi_tovar_risunok(dizayn)`.id_cut AND

  `oboi_tovar_stily`.id_product = t1.id AND
  `oboi_tovar_oblasty_primeneniya`.id_product = t1.id AND
  `oboi_tovar_osobennosty`.id_product = t1.id AND
  `oboi_tovar_oboi_dlya`.id_product = t1.id AND
  `oboi_tovar_risunok(dizayn)`.id_product = t1.id AND

  `oboi_tovar_stily`.id_cut = 1 AND
  `oboi_tovar_oblasty_primeneniya`.id_cut = 1 AND
  `oboi_tovar_osobennosty`.id_cut = 1 AND
  `oboi_tovar_oboi_dlya`.id_cut = 1 AND
  `oboi_tovar_risunok(dizayn)`.id_cut = 1

Indicate the direction, how to complete the selection, then something does not come to mind at all, what to look at

Answer 1

What a gesture you have written.
To begin with, what you select in a row of tables is inside mysql is an inner join. You also need a left join. Then, if there is no entry in the table, the product will still be selected.
Further, the general form of the request will be as follows:

SELECT t.* FROM tovar
LEFT JOIN svyazka1 as s1 ON (s1.id_from_tovar = t.id)
LEFT JOIN svyazka2 as s2 ON (s2.id_from_tovar = t.id)
/* .. еще join при необходимости.. */
WHERE 
s1.category_id = 1 /* ID искомой категории внутри связки */
AND 
s2.category_id = 2 /* ID искомой категории внутри связки */

but in this request there is one BUT. Do not join here the names of the categories for which the search is being carried out (but is it necessary?). There are two options.

• Separate small requests to select the names of categories for the product (not very good, but not critical, tables are usually small with a list of categories).
  
• Create an additional field in the table with products, in which to store the "cache" (json/serialized) of the list of categories and their IDs in order to quickly display them without accessing the database. In this case, additional complexity - when changing the category table, you will have to rebuild the cache in the product table. and keep it up to date
  

Answer 2

Let me guess? Half of the indicated fields in the tovar table (or even 3/4) allow null and for each product there are enough fields with a null value.
From the point of view of the architecture of the base and its normal forms, I would beat them as soon as possible.
I think what is needed here is a properties table with fields - prop_id, category_id, isNull (required or not) and something else like that. And one more table properties_value with fields tovarId, propId, value. The number of JOINs is increasing, but tables are getting simpler and cleaner.
And the request itself is trash, which is faster to burn and write again than to edit.