Post reply

Select the difference of a qurey

  • 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???

  • November 1, 2011 at 11:50 am

    #1401759

    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?

  • November 1, 2011 at 11:51 am

    #1401760

    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

  • November 1, 2011 at 12:43 pm

    #1401788

    Thank You....

  • November 1, 2011 at 12:47 pm

    #1401794

    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

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

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