with new_items as  (select i.item_id as new_item, i.name as new_item_name, i.external_id as guid, ic.icode as new_item_barcode from t_item i , t_icode ic where 1 = 1 and i.item_id = ic.item_id and i.cat_id = 2000),
  all_items as (select count(new_item) over(partition by  new_item_name), * from new_items ni inner join (select i1.item_id, i1.cat_id as box_cat, i1.article, i1.name,  ic1.icode from t_item i1 inner join t_icode ic1 on ic1.item_id = i1.item_id where i1.cat_id not in (2000, 1999, 1998, 1997) and i1.name like '%Коробка%') as items on ni.new_item_barcode = items.icode)
    select a.new_item,
           il.object_id, a.new_item_name, i.image_name, i.image_path, a.new_item_barcode, a.icode, a.item_id as box_id, a.box_cat, a.article, a.name from all_items a
      left join t_image_link il on a.box_cat = il.object_id and il.object_type_id in (9)
      left join t_image i on i.image_id = il.image_id
 order by box_cat  ;


---- Схемы изображения схем к коробкам
with new_items as  (select i.item_id as new_item, i.name as new_item_name, i.external_id as guid, ic.icode as new_item_barcode from t_item i , t_icode ic where 1 = 1 and i.item_id = ic.item_id and i.cat_id = 2000),
  all_items as (select count(new_item) over(partition by  new_item_name), * from new_items ni inner join (select i1.item_id, i1.cat_id as box_cat, i1.article, i1.name,  ic1.icode from t_item i1 inner join t_icode ic1 on ic1.item_id = i1.item_id where i1.cat_id not in (2000, 1999, 1998, 1997) and i1.name like '%Коробка%') as items on ni.new_item_barcode = items.icode)
    select a.new_item, ip.prop_value_id, pv.brief, i.image_name, i.image_path, il.object_id, ip.prop_id, a.new_item_name, a.new_item_barcode, a.icode, a.item_id as box_id, a.box_cat, a.article, a.name from all_items a
      left join t_item_prop ip on ip.item_id = a.item_id
      left join t_prop p on p.prop_id = ip.prop_id
      left join t_prop_value pv on pv.prop_value_id = ip.prop_value_id
      left join t_image_link il on il.object_id = pv.prop_value_id  and il.object_type_id in (13)
      left join t_image i on i.image_id = il.image_id
where p.prop_id = 23;