SELECT property.product_id AS p_id FROM property AS pr WHERE  (pr.name='цвет' AND pr.value = 'красный')

SELECT prod.name, prop.value FROM product AS prod JOIN property AS prop ON prop.product_id = prod.id

create table prod (
  id integer primary key autoincrement,
  name varchar
);

create table prop (
  id integer primary key autoincrement,
  prod_id   integer,
  name varchar,
  value varchar,
  foreign key(prod_id) references prod(id)
);

insert into prod (name) values ('hat');
insert into prod (name) values ('t-shirt');
insert into prod (name) values ('gloves');

insert into prop (prod_id, name, value) values (1, 'color' ,'red');
insert into prop (prod_id, name, value) values (1, 'material' ,'whool');
insert into prop (prod_id, name, value) values (1, 'condition', 'new');
insert into prop (prod_id, name, value) values (2, 'condition', 'new');
insert into prop (prod_id, name, value) values (2, 'size', 'xxl');
insert into prop (prod_id, name, value) values (2, 'material', 'cotton');
insert into prop (prod_id, name, value) values (3, 'condition', 'old');

SELECT prod.id, prod.name AS prod_name, prop.name AS prop_name, prop.value AS value FROM prod 
           JOIN prop ON prod.id = prop.prod_id WHERE (prop.name = 'condition' AND prop.value = 'new')

select prod.*, prop.name, prop-value from prod-prop pp
join prod on prod.id = pp.prod_id
join prop on prop.id = pp.prop_id and (prop.name = 'size' and pp.prop-value = 'xxl') and (prop.name = 'condition' and pp.prop-value = 'new')

   create view prod_prop as 
   select prod.*, prop.name, prop.value from prod
       join prop on prop.prod_id = prod.id

    select * from prod_prop 
    join prod_prop pp on prod_prop.id = pp.id and (pp.name = 'size' and pp.value = 'xxl') and (pp.name = 'condition' and pp.value = 'new')

   
create view prod_prop as 
   select prod.*, prop.name as p_name, prop.value from prod
       join prop on prop.prod_id = prod.id

    select * from prod_prop 
    join (
           select id from prod_prop WHERE (prod_prop.p_name = 'size' and prod_prop.value = 'xxl') 
           intersect
           select id from prod_prop WHERE (prod_prop.p_name = 'condition' and prod_prop.value = 'new')
    ) ids
          on ids.id = prod_prop.id

create table prod (
  id integer primary key autoincrement,
  header varchar,
  desc varchar
); 

create table prop (
  id integer primary key autoincrement,
  prod_id integer,
  dict_id integer,
  value varchar,
  float_value float,

  foreign key(prod_id) references prod(id),
  foreign key(dict_id) references dict(id)
);

create table dict (
  id integer primary key autoincrement,
  name varchar,
  rus_name varchar,
  type varchar,
  units varchar,
  sorting integer
);

select prop_key from prop_name pn where pn.name = 'material'