none
Need help on SQL select RRS feed

  • Question

  • Hi all,

    I have data as below and would like to run a query to extract data as the example below.

    ID1      ID2       Month     Prod#
    001                      11                  0S3
    003      001          1                    0S3
    004                      1                    0S3
    005      004          3                    0S3
    006                      3                    0S3

    ID1      ID2        Month     Prod#
    007                      5                    0S5
    008      007          5                    0S5
    009                      5                    0S5

    1st condition:

    If ID1 is in ID2 NOT same month, same PROD,  I would like to load them
    001                      11                  0S3
    003      001          1                    0S3
    004                      1                    0S3
    005      004          3                    0S3
    the id 006 has the same PROD# but has no data in ID 2 .. would like to load it too
    006                      3                    0S3

    2nd condition

    If ID1 is in ID2 have the same month, same PROD# , Please not load data
    007                     5                    0S5
    008      007          5                    0S5
    Load the below as it doen't have ID2 but same Prod#
    009                      5                    0S5

    The result should be
    ID1      ID2       Month     Prod#
    001                     11                 0S3
    003      001          1                   0S3
    004                     1                   0S3
    005      004          3                   0S3
    006                     3                   0S3

    009                      5                  0S5

    Please advise.  Thanks so much.

    Ddee


    Sunday, December 8, 2019 5:52 PM

Answers

  • Had you posted your sample data as CREATE TABLE + INSERT I would have tested this. Now I leave the onus of testing and further refinement to you.

    SELECT *
    FROM   tbl a
    WHERE  NOT EXISTS (SELECT *
                       FROM  tbl b
                       WHERE b.prod = a.prod
                         AND b.ID2  = a.ID1
                         AND b.month = a.month)
        AND NOT EXISTS (SELECT *
                       FROM  tbl b
                       WHERE b.prod = a.prod
                         AND b.ID1  = a.ID2
                         AND b.month = a.month)

     


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by ddee Sunday, December 8, 2019 9:36 PM
    Sunday, December 8, 2019 7:30 PM

All replies

  • Had you posted your sample data as CREATE TABLE + INSERT I would have tested this. Now I leave the onus of testing and further refinement to you.

    SELECT *
    FROM   tbl a
    WHERE  NOT EXISTS (SELECT *
                       FROM  tbl b
                       WHERE b.prod = a.prod
                         AND b.ID2  = a.ID1
                         AND b.month = a.month)
        AND NOT EXISTS (SELECT *
                       FROM  tbl b
                       WHERE b.prod = a.prod
                         AND b.ID1  = a.ID2
                         AND b.month = a.month)

     


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by ddee Sunday, December 8, 2019 9:36 PM
    Sunday, December 8, 2019 7:30 PM
  • Thanks Erland - It works perfectly.

    Ddee

    Sunday, December 8, 2019 9:37 PM