Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Another Color Wheel

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.

Groups Colors Numbers
A RED 1
A GREEN 2
A BLUE 3
B RED 1
B YELLOW 4
C BLUE 3

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.

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.

Comments

Posted by Joe Celko on 9 November 2011

>>  The requirements for that problem lent themselves to a solution involving bitwise operations (which you can read here). <<

We do not do bit operations in SQL; that was low-level assembly language programming and it has no place in RDBMS. Shame on you!

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be dialect free. Here is my guess at what you might have meant:

CREATE TABLE ColorWheel

(group_name CHAR(1) NOT NULL,

color_name VARCHAR(12) NOT NULL,

PRIMARY KEY (group_name, color_name));

That color number you had is redundant. Why dpo you have Normalizations?  The right ay would be a Land or Pantone look up table using those codes. But let's stick with the name as part of the key. Let's use the modern row consructors:

INSERT INTO ColorWheel

VALUES

('A', 'RED'), ('A', 'GREEN'), ('A', 'BLUE'),

('B', 'RED'), ('B', 'YELLOW'), ('C', 'BLUE');

>> produce those groups and color combinations that do not exist in the provided group/starting group. <<

That is a set operation, so use set operators.

SELECT X.group_name, X.color_name

 FROM (SELECT group_name, color_name

         FROM ColorWheel AS CW1

       EXCEPT

       SELECT group_name, color_name

         FROM ColorWheel AS CW2

        WHERE group_name = @in_search_group)

      AS X(group_name, color_name);

Posted by Jason Brimhall on 9 November 2011

Frankly Celko, this was an answer to a forum question.  And as I stated - this was to illustrate the example and formal DDL was not used intentionally.  NOTE the use of the CTE.

Posted by JD on 9 November 2011

Celko's solution doesn't return the correct answer.  Seriously, does this guy know anything?

Posted by Jason Brimhall on 9 November 2011

@Ray - I know where he is coming from with his post.  He does know quite a bit.  Some times his comments come off the wrong way ;)

Leave a Comment

Please register or log in to leave a comment.