Select the difference of a qurey

Question

Select the difference of a qurey

tp 25072

SSC Veteran

Points: 262
More actions
November 1, 2011 at 11:17 am

#262184

Hello,
Lets say I have a table with 3 columns:
Groups Colors Numbers
A RED 1
A GREEN 2
A BLUE 3
B RED 1
B YELLOW 4
C BLUE 3
What I need to do here is Select all the Colors+Numbers of the other groups which are not part of the current group.
So if I belong to Group A, I need to write a Query which would return : B YELLOW 4
We don't care about Group C, because (BLUE 3) is already part of group A.
I can't figure this out. I am thinking something like this:
SELECT DISTINCT COLORS, NUMBERS FROM TABLE
WHERE NOT EXISTS (SELECT DISTINCT COLORS, NUMBERS FROM TABLE WHERE Groups = 'A')
Any ideas, Please???

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

EXISTS doesn't work that way. you could make it work, but I prefer using the EXCEPT syntax in this case (since you're using 2008). Something like:

declare @currentgrp char(1)

set @currentgrp='A'

select color,nbr

from grpColors where grp<>@currentgrp

EXCEPT

select color,nbr

from grpColors where grp=@currentgrp

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Try this

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)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tp 25072 SSC Veteran Points: 262 More actions · Answer 3

tp 25072

SSC Veteran

Points: 262

November 1, 2011 at 12:43 pm

#1401788

Thank You....

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events