0

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!

  • This is called Relational Division, there are several solutions to it, the one from @LaughingVergil is usually good performer. – dnoeth Sep 15 '17 at 7:33
  • Relational Division - thank you! I was struggling with my google-fu on this problem, hence my post. Now I am finding many useful articles. – Robot_Overlord Sep 15 '17 at 12:46
0

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.

  • 1
    Should be either two COUNT(*) or two COUNT(DISTINCT), but not mixed :-) – dnoeth Sep 15 '17 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.