title: How to identify unique items within a dataset defined by different attributes?
body: <p>I have the following dataset of items</p>
  
  <p>items:</p>
  
  <pre><code>item
  i1
  i2
  i3
  i4
  </code></pre>
  
  <p>attributes</p>
  
  <pre><code>item | attribute | attr_group
  i1     green       color
  i2     red         color
  i1     fancy       style
  i2     cool        style
  i4     purple      color
  i3     red         color
  i1     REF1        refid
  i2     REF2        refid
  i3     REF2        refid
  i4     REF1        refid
  </code></pre>
  
  <p>attribute group names</p>
  
  <pre><code>attr | group
  a1     color
  a2     style
  a3     refid
  </code></pre>
  
  <p>articles:</p>
  
  <pre><code>article_id | title ..
  ref1_1
  ref1_2
  ref2
  </code></pre>
  
  <p>Now I want to fill table articles with unique items identified by <code>article_id</code>. A unique item is defined as an item which has a refid that is either with no attributes or has more then one different color.</p>
  
  <p>e.g. There are two items with ref1 (green and purple), therefore ref1 can not be unique in itself but will have 2 article_ids: ref1_1 and ref1_2.</p>
  
  <p>The items which have ref2 are of the same color, therefore there is only one article_id to be defined.</p>
  
  <p>How could a possible MySQL query look like to select unique items and insert them into the table articles? I can't wrap my head around this any help is greatly appreciated. </p>
  
  <p>My best attempt looks like the following SQL query, however it will only define articles based on ref_id and color, what is missing are the items that have all the same color.</p>
  
  <pre><code>/* create unique articles */
  INSERT
  IGNORE INTO articles (article_id, URL, ref_id, cat_id, dial_id)
  SELECT CONCAT ( a.value, '-', a2.value) AS article_id,
         CONCAT ( cs.name_safe, '-', LOWER(ca.attr_de), '-r', a.value) AS URL,
         a.value AS refid,
         cs.ID,
         a2.value AS color_id
  FROM `classifieds_attr` a
  INNER JOIN classifieds c ON c.ID = a.cl_id
  INNER JOIN cat_names_sub cs ON c.cat_id = cs.ID
  INNER JOIN `classifieds_attr` a2 ON c.ID = a2.cl_id
  AND a2.attr_group_id = 9
  INNER JOIN cat_attr ca ON a2.value = ca.attr_id
  WHERE a.attr_group_id = 8
    AND cs.cat_main_id = 15
    AND a2.value NOT LIKE 'Invalid:%'
  GROUP BY refid,color_id
  </code></pre>