How do you find the characteristics of `iblock_property_id` that are not available in this table?



  • There is a table containing properties, iblock_element_property

    id- unique number
    iblock_property_id - id properties
    iblock_element_id - product.
    value - S

    How to find iblock_element_idwhose properties iblock_property_id=654which are not available in this table? Here's a picture of the right data. iblock_element_id =1 is the productiblock_element_id =2 is a trade proposal, They contain a set of identical artic properties. (Service) iblock_property_id=654 for goods and iblock_property_id=718 The trade proposal, the VALUE values of these sts shall be always and equal among themselves. The trade offer has a characteristic of the product. iblock_property_id=712) введите сюда описание изображения Next, this is a picture of bad data. введите сюда описание изображения In short, there's a trade offer, there's no product, so it needs to be added. Question-how?



  • The base looks like https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model so I guess there's a table like that. iblock_elementwhich contains all the "trues" (products and trade proposals) referred to iblock_element_id♪ If not, it can be replaced by SELECT DISTINCT iblock_element_id FROM iblock_element_property (It is assumed that the goods have properties other than the articula).

    The problem is two parts: first, we need to choose all the goods that have no properties. 654and then we need to find the trade proposals they need.

    SELECT 654, goods.iblock_element_id, ep_offer_article.value
      FROM 
             ( iblock_element goods
            LEFT JOIN iblock_element_property ep_goods
              ON goods.iblock_element_id = ep_goods.iblock_element_id
                AND ep_goods.iblock_property_id = 654
             )
        JOIN 
             ( iblock_element offers
            JOIN iblock_element_property ep_offer_goods_link
              ON offers.iblock_element_id = ep_offer_goods_link.iblock_element_id
                AND ep_offer_goods_link.iblock_property_id = 712
            JOIN iblock_element_property ep_offer_article 
              ON offers.iblock_element_id = ep_offer_article.iblock_element_id
                AND ep_offer_article.iblock_property_id = 718
             )
          ON ep_offer_goods_link.value = goods.iblock_element_id
      WHERE ep_goods.iblock_property_id IS NULL -- не задано свойство 654
    

    First Group JOIN The list of tables selects the essence and properties of 654 (which may not be used, so that LEFT JOINthe second is the substance of the goods (712) and the artiquele (718) (all of these properties shall be present), after which all intersects and links to the goods. Condition WHERE leaves only the goods without the articula in extradition.

    http://dev.mysql.com/doc/refman/5.7/en/insert-select.html Sign it yourself.

    Example on sqlfiddle.com: http://sqlfiddle.com/#!9/6c68e/7




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2