SELECT from Many-to-Many Columns Unique Values from Column 1 and Non-duplicate from Column2

  • Hi

    I am having a situation where I need to pick from a data set like the one below a unique value from alert_code and associate a non duplicated product_code

    Dataset

    idalert_codeproduct_code

    1A 123

    4A 456

    7A 789

    2B 123

    5B 456

    8B 789

    3C 123

    6C 456

    9C 789

    Result Should be for example

    1)

    A 123

    B 456

    C 789

    OR

    2)

    A 456

    B 789

    C 123

    OR other combinations that meet the requirement of displaying all alert codes and within each alert code a product code exist within the alert code and that not exist in other alert codes.

    There may be many other Alert_codes and many other Product Codes in a Many to Many relation.

    Not sure how to handle this query.

    Any help will be greatly appreciated

    Regards

  • Hi and welcome to the forums. This is a great place to start for getting you an answer. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • as an example of how to present some sample data see below and here https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    CREATE TABLE #yourtable(

    id INTEGER NOT NULL

    ,alert_code VARCHAR(1) NOT NULL

    ,product_code INTEGER NOT NULL

    );

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);

    SELECT * FROM #yourtable ORDER BY ID

    do you ALWAYS have the same number of rows for each "alert_code"?

    what do you want as results if you add these extra rows?

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • This MIGHT work but I'm not sure since I'm having a hard time understanding your requirement and I have a feeling your example data set isn't reflective of what it really looks like. That being said...

    CREATE TABLE #yourtable(

    id INTEGER NOT NULL

    ,alert_code VARCHAR(1) NOT NULL

    ,product_code INTEGER NOT NULL

    );

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);

    --SELECT * FROM #yourtable ORDER BY ID

    SELECT

    x.alert_code,

    x.product_code

    FROM (

    SELECT

    alert_code,

    product_code,

    DENSE_RANK() OVER (PARTITION BY product_code ORDER BY alert_code) AS MyRank1,

    DENSE_RANK() OVER (PARTITION BY alert_code ORDER BY product_code) AS MyRank2

    FROM #yourtable

    ) x

    WHERE x.MyRank1 = x.MyRank2

    DROP TABLE #yourtable

    Cheers,


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • yb751 (6/1/2016)


    This MIGHT work but I'm not sure since I'm having a hard time understanding your requirement and I have a feeling your example data set isn't reflective of what it really looks like. That being said...

    CREATE TABLE #yourtable(

    id INTEGER NOT NULL

    ,alert_code VARCHAR(1) NOT NULL

    ,product_code INTEGER NOT NULL

    );

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);

    --SELECT * FROM #yourtable ORDER BY ID

    SELECT

    x.alert_code,

    x.product_code

    FROM (

    SELECT

    alert_code,

    product_code,

    RANK() OVER (PARTITION BY product_code ORDER BY alert_code) AS MyRank1,

    RANK() OVER (PARTITION BY alert_code ORDER BY product_code) AS MyRank2

    FROM #yourtable

    ) x

    WHERE x.MyRank1 = x.MyRank2

    DROP TABLE #yourtable

    Cheers,

    I would use DENSE_RANK() rather than RANK().

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (6/1/2016)

    I would use DENSE_RANK() rather than RANK().

    Drew

    Good Point, no way of knowing of if the data has duplicate alert/product code pairs. I'll edit the code.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • some more testdata to play with

    CREATE TABLE #yourtable(

    id INTEGER NOT NULL

    ,alert_code VARCHAR(1) NOT NULL

    ,product_code INTEGER NOT NULL

    );

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (12,'F',1);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (13,'G',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (14,'H',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (15,'A',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (16,'B',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (17,'C',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (18,'D',999);

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • hmmm...yeah that throws a wrench in things. I just wished we knew more.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • yb751 (6/1/2016)


    hmmm...yeah that throws a wrench in things. I just wished we knew more.

    hehe....pity the OP didnt post an update when (s)he peeked out from behind the sofa, half an hour ago 🙂

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Ok so after a few voodoo spells and a couple chickens later I came up with this. If it looks weird, that's because it is. I'm pretty sure this can be reworked but for whatever it's worth.

    The following code will give you a unique product code for each alert code amongst those that qualify. However, there is no guarantee which valid product will be selected.

    CREATE TABLE #yourtable(

    id INTEGER NOT NULL

    ,alert_code VARCHAR(1) NOT NULL

    ,product_code INTEGER NOT NULL

    );

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (12,'F',1);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (13,'G',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (14,'H',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (15,'A',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (16,'B',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (17,'C',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (18,'D',999);

    --SELECT * FROM #yourtable ORDER BY ID

    SELECT

    z.alert_code,

    z.product_code,

    ROW_NUMBER() OVER (PARTITION BY z.product_code ORDER BY z.alert_code) AS FinalRank

    INTO #final

    FROM (

    SELECT

    y.alert_code,

    y.product_code,

    DENSE_RANK() OVER (ORDER BY alert_code) AS MyRank1,

    ROW_NUMBER() OVER (PARTITION BY y.alert_code ORDER BY y.alert_code) AS MyRank2

    FROM #yourtable y

    CROSS APPLY(

    SELECT TOP (SELECT DISTINCT COUNT(product_code) FROM #yourtable) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS maxValues FROM dbo.SysColumns sc1, dbo.SysColumns sc2

    ) x

    ) z

    WHERE z.MyRank1 = z.MyRank2

    SELECT alert_code, product_code FROM #final WHERE FinalRank = 1 ORDER BY alert_code

    DROP TABLE #yourtable

    DROP TABLE #final


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • yb751 (6/1/2016)


    Ok so after a few voodoo spells and a couple chickens later I came up with this. If it looks weird, that's because it is. I'm pretty sure this can be reworked but for whatever it's worth.

    The following code will give you a unique product code for each alert code amongst those that qualify. However, there is no guarantee which valid product will be selected.

    CREATE TABLE #yourtable(

    id INTEGER NOT NULL

    ,alert_code VARCHAR(1) NOT NULL

    ,product_code INTEGER NOT NULL

    );

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (12,'F',1);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (13,'G',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (14,'H',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (15,'A',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (16,'B',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (17,'C',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (18,'D',999);

    --SELECT * FROM #yourtable ORDER BY ID

    SELECT

    z.alert_code,

    z.product_code,

    ROW_NUMBER() OVER (PARTITION BY z.product_code ORDER BY z.alert_code) AS FinalRank

    INTO #final

    FROM (

    SELECT

    y.alert_code,

    y.product_code,

    DENSE_RANK() OVER (ORDER BY alert_code) AS MyRank1,

    ROW_NUMBER() OVER (PARTITION BY y.alert_code ORDER BY y.alert_code) AS MyRank2

    FROM #yourtable y

    CROSS APPLY(

    SELECT TOP (SELECT DISTINCT COUNT(product_code) FROM #yourtable) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS maxValues FROM dbo.SysColumns sc1, dbo.SysColumns sc2

    ) x

    ) z

    WHERE z.MyRank1 = z.MyRank2

    SELECT alert_code, product_code FROM #final WHERE FinalRank = 1 ORDER BY alert_code

    DROP TABLE #yourtable

    DROP TABLE #final

    oh, how I love these lateral thinking problems 😛

    if only the OP would post back with a sample data set that covers all possibilites then I could sleep easy :w00t:

    looking at your latest code I ma not sure that it delivers what is required

    I need to pick from a data set like the one below a unique value from alert_code and associate a non duplicated product_code

    surely that means that all 8 "alert_codes" are included in the result set

    SELECT DISTINCT alert_code FROM #yourtable

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL (6/1/2016)

    oh, how I love these lateral thinking problems 😛

    if only the OP would post back with a sample data set that covers all possibilites then I could sleep easy :w00t:

    looking at your latest code I ma not sure that it delivers what is required

    I need to pick from a data set like the one below a unique value from alert_code and associate a non duplicated product_code

    surely that means that all 8 "alert_codes" are included in the result set

    SELECT DISTINCT alert_code FROM #yourtable

    LOL...yes, I do agree that it's not perfect but I'm purely working off the data you provided for fun (or torture). In that scenario it would be impossible to provide a unique product code for each available alert code. That being said I'm off the case until we get more info. 😉


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • yb751 (6/1/2016)


    Ok so after a few voodoo spells and a couple chickens later I came up with this. If it looks weird, that's because it is. I'm pretty sure this can be reworked but for whatever it's worth.

    The following code will give you a unique product code for each alert code amongst those that qualify. However, there is no guarantee which valid product will be selected.

    CREATE TABLE #yourtable(

    id INTEGER NOT NULL

    ,alert_code VARCHAR(1) NOT NULL

    ,product_code INTEGER NOT NULL

    );

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (12,'F',1);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (13,'G',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (14,'H',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (15,'A',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (16,'B',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (17,'C',999);

    INSERT INTO #yourtable(id,alert_code,product_code) VALUES (18,'D',999);

    --SELECT * FROM #yourtable ORDER BY ID

    SELECT

    z.alert_code,

    z.product_code,

    ROW_NUMBER() OVER (PARTITION BY z.product_code ORDER BY z.alert_code) AS FinalRank

    INTO #final

    FROM (

    SELECT

    y.alert_code,

    y.product_code,

    DENSE_RANK() OVER (ORDER BY alert_code) AS MyRank1,

    ROW_NUMBER() OVER (PARTITION BY y.alert_code ORDER BY y.alert_code) AS MyRank2

    FROM #yourtable y

    CROSS APPLY(

    SELECT TOP (SELECT DISTINCT COUNT(product_code) FROM #yourtable) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS maxValues FROM dbo.SysColumns sc1, dbo.SysColumns sc2

    ) x

    ) z

    WHERE z.MyRank1 = z.MyRank2

    SELECT alert_code, product_code FROM #final WHERE FinalRank = 1 ORDER BY alert_code

    DROP TABLE #yourtable

    DROP TABLE #final

    I don't like it, because G is excluded and 456 is also excluded and both can be included if you change B - 999 to B - 456. Of course, I'm not the OP, and they may love it.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Trust me, I'm not crazy about it either. I have a couple of other ideas but until I hear from the OP I'd just be taking more shots in the dark.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Thanks for your prompt response.

    I would like to provide more details to the requirements.

    In my challenge, 1 product can has thousands of alerts and I need to report all Alert_Codes and select for each alert a product_code that hasn't been reported in previous alert

    The idea is that all alert_codes should appear once A,B,C...ZZZZZ. For each alert_code I should display 1 of the infinite product_code numbers associated to the alert_code, the only condition is that they do not repeat in the display. The situation is that many of the product_code are repeated in every alert_code and not able to select not repeated. I am trying not to use CURSORS, which would solve the challenge.

    So, the final output should be something like

    A->123

    B->456

    C->789

    D->135

    E->246

    .

    .

    AA->999

    AZ->9999

    .

    .

    ZZZZZ->9999999

    etc...

    The alert_code is an integer in my real scenario, but I just decided to use letters to make simpler.

    The product_code is an integer in my real scenario, I just used 3 digits to make it simpler and easy to differentiate.

    I hope my requirements are better explained.

    Thanks for your help.

Viewing 15 posts - 1 through 15 (of 17 total)

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