Some time ago, I had the opportunity to work on a problem involving the color wheel.  The requirements for that problem lent themselves to a solution involving bitwise operations (which you can read here).

I recently came across a new problem involving the color wheel.  This time the requirements and solution are different than the last.  So what better opportunity than the present to write about it.

This time we are presented with a variation where we have been introduced to a group.  The group has various colors.  Given a group, I need to find the other groups that contain a color that the given group does not contain.

Frankly, the solution to this problem is far easier than I initially set out to make it.  Memory being as it is, I did the same thing with the Color Wheel the last time.  I had to revisit that one and update that solution.

This time around, I have simplified the solution prior to writing about it.  That said, somebody will probably show me an easier way to solve it.

Setup

Here is the table with the data that we will be using.

Solution

I elected to utilize a CTE rather than formalize a table for the extraction of this sample.  The following solution will thus be using that CTE rather than a table.

Code block

DECLARE @startgroup CHAR(1) = 'B'
;
WITH colorwheel (Groups,Colors,Numbers) AS (
SELECT 'A', 'RED', 1
UNION ALL
SELECT 'A', 'GREEN', 2
UNION ALL
SELECT 'A', 'BLUE', 3
UNION ALL
SELECT 'B', 'RED', 1
UNION ALL
SELECT 'B', 'YELLOW', 4
UNION ALL
SELECT 'C', 'BLUE', 3
)
 
SELECT Groups,COLORS, cw.NUMBERS
FROM colorwheel cw
WHERE cw.Numbers NOT IN (SELECT Numbers FROM colorwheel WHERE Groups = @startgroup)

Notice the use of a subquery.  This subquery represents a derived table of Color IDs (since the Numbers column is representative of IDs of the colors).  This is the set of data we want to compare against.  If the color is in this derived table, then we do not want that data in our final result set.

With this setup, I can change the group via the @startgroup variable.  I can now meet the criteria thanks to the “NOT IN” and only produce those groups and color combinations that do not exist in the provided group/starting group.

See, it really is not too difficult.  Breaking down what the requirements are, we can simplify the query and produce the desired results.