title: Having trouble with database schema causing logical issues using NOT LIKE with AND statements
body: <p>Table information</p>
  
  <p><code>dbo.Attribute</code> (stores product attributes)</p>
  
  <pre><code>Item_Number, ATTR, VALUE
  </code></pre>
  
  <p><code>dbo.Information</code> (sales data)</p>
  
  <pre><code>Item_Number,Title, Sold_Price
  </code></pre>
  
  <p>Query:</p>
  
  <pre><code>SELECT 
     Attribute.Item_Number, lower (Information.Item_Title)
     , min(Information.Sold_Price) as Price
  FROM   
     Attribute 
  INNER JOIN
     Information ON Attribute.Item_Number = Information.Item_Number
  </code></pre>
  
  <p>So the purpose of this query is that we have a bunch of sales data we'd like to cull to see what items we're not selling yet. For the sake of simplicity lets pretend we're selling hammers. <code>Item_Number</code> ties together sales &amp; attribute data. They represent the same item.</p>
  
  <p>We first filter out related sales data that we're not interested in, for instance Hammer Holsters.  </p>
  
  <pre><code>where Information.Title not like '%holster%'
  </code></pre>
  
  <p>then we include all of the remaining hammer sales info</p>
  
  <pre><code>and Information.Title like '%hammer%'
  </code></pre>
  
  <p>Lastly we're trying to filter out the UPCs of the various hammers using queries like this</p>
  
  <pre><code>and REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Attribute.VALUE, '-', ''), ' ', ''), '\\', ''), '/', ''), '&amp;', ''), ',', '') not like '%HC456%'
  and REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Attribute.VALUE, '-', ''), ' ', ''), '\\', ''), '/', ''), '&amp;', ''), ',', '') not like '%HC457%'
  </code></pre>
  
  <p>(I have since learned that using + between the words is more eloquent than repeating the whole line)</p>
  
  <p>To ignore all results for certain hammers we're already selling. However, this is just removing the lines (rows) that contain this, not the whole <code>ITEM_NUMBER</code> which is what we're trying to exclude.</p>
  
  <p>So for this example data set</p>
  
  <pre><code>Item_Number |   ATTR   | VALUE
  ------------+----------+--------
  4789888     | UPC      | HC456
  4789888     | Material | Leather
  </code></pre>
  
  <p>It is excluding the line with the UPC, but not the Item_Number entirely.</p>
  
  <p>The the result is this output</p>
  
  <pre><code> 4789888, Material, Leather
  </code></pre>
  
  <p>How do I exclude all results for the Item_Number when there is a NOT LIKE match on Attribute.VALUE?</p>
  
  <p>And is there a more eloquent way of ignoring everything except for alphanumeric information in a search than the way shown?</p>