Sql-server – Find common attributes among given products in SQL Server

sql serversql-server-2008-r2

I am trying to find all attributes that are common to a given set of products (Stock_ID numbers). I have a table called Stock_Attributes that contains product info. It looks something like this:

+----------+--------------+------------+
| Stock_ID | Attribute_ID | Value      |
+----------+--------------+------------+
|     1001 |            2 | Farmhouse  |
|     1001 |            8 | Blue       |
|     1001 |            9 | Bronze     |
|     1001 |           10 | Metal      |
|     1001 |           10 | Wood       |
|     1002 |            2 | Industrial |
|     1002 |            9 | Chrome     |
|     1002 |           10 | Metal      |
|     1003 |            2 | Industrial |
|     1003 |            8 | Red        |
|     1003 |           10 | Glass      |
|     1003 |           10 | Metal      |
+----------+--------------+------------+

Stock_ID and Attribute_ID are both have INT as the data type. Value is a sql_variant since some attributes are Dates or Bits. As you can see, any product can have any combination of attributes and more than one attribute of the same type. The desired result for the above sample set would be one just one row, since these products only have one attribute in common (typically there will be several in common):

+--------------+------------+
| Attribute_ID | Value      |
+--------------+------------+
|           10 | Metal      |
+--------------+------------+

I do not need to return the Stock_ID numbers in this query since we are starting with that information in a temporary table called #TempItemNumbers. I simply need to return a set of Attribute_IDs and Values that are common to all Stock_ID numbers listed in #TempItemNumbers, which is generated via user input.
I do not typically use cursors or dynamic SQL so I am not very familiar with these methods, but I'm thinking this problem will require one or both.
Please help!

Best Answer

Use a COUNT() on the rows in your temp table to simplify your query, e.g.:

SELECT 
    sa.Attribute_ID, 
    sa.Value, 
    Count(DISTINCT sa.Stock_ID) as Matches
FROM Stock_Attributes sa
INNER JOIN #TempItemNumbers tin
    ON tin.Stock_ID = sa.Stock_ID
GROUP BY sa.Attribute_id,
    sa.Value
HAVING Count(*) = (SELECT Count(*) FROM #TempItemNumbers)

Only those rows where all the items have matching attributes will have the same count as the number of items in your temp table. Making the COUNT() be of the distinct Stock_IDs handles duplicate entries for a given Attribute/Value pair for an item - just in case. You can replace that with a simple COUNT(*) if you are guaranteed not to have duplicates.

Related Question