SQL Query : Help with usage of NOT IN
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL Query : Help with usage of NOT IN

  1. #1
    Join Date
    Nov 2003
    Posts
    31

    SQL Query : Help with usage of NOT IN

    Hi,

    I would like to request help with a SQL query as explained below:

    I have a table with the following structure.

    CREATE TABLE RESULT (
    ID VARCHAR2(10),
    SERIAL INTEGER,
    ACTIVE CHAR(1)
    );

    The table has data as follows:

    INSERT INTO RESULT VALUES ('1', 1, 'Y');
    INSERT INTO RESULT VALUES ('1', 2, 'Y');
    INSERT INTO RESULT VALUES ('1', 3, 'Y');

    INSERT INTO RESULT VALUES ('2', 4, 'Y');
    INSERT INTO RESULT VALUES ('2', 5, 'Y');
    INSERT INTO RESULT VALUES ('2', 6, 'Y');

    INSERT INTO RESULT VALUES ('3', 7, 'Y');
    INSERT INTO RESULT VALUES ('3', 8, 'Y');
    INSERT INTO RESULT VALUES ('3', 9, 'Y');

    For each of the IDs, I have SERIAL numbers from 1 thro' 10.

    What I need to add to the existing data is all the other rows with an active indicator
    of N.

    In other words, for each of the IDs I should have 10 rows of data. Three rows are already inserted with the Y indicator. I need to insert the rest of the 7 rows with the appropriate SERIAL and ACTIVE indicators.

    In other words, after the above required insertion, if I select from the RESULT table for a given ID, I should have 10 rows as follows:

    SQL> SELECT * FROM RESULT WHERE ID='1';

    ID SERIAL ACTIVE
    == ====== ======
    1 1 Y
    1 2 Y
    1 3 Y
    1 4 N
    1 5 N
    1 6 N
    1 7 N
    1 8 N
    1 9 N
    1 10 N

    SQL> SELECT * FROM RESULT WHERE ID='2';

    ID SERIAL ACTIVE
    == ====== ======
    2 1 N
    2 2 N
    2 3 N
    2 4 Y
    2 5 Y
    2 6 Y
    2 7 N
    2 8 N
    2 9 N
    2 10 N

    SQL> SELECT * FROM RESULT WHERE ID='3';

    ID SERIAL ACTIVE
    == ====== ======
    3 1 N
    3 2 N
    3 3 N
    3 4 N
    3 5 N
    3 6 N
    3 7 Y
    3 8 Y
    3 9 Y
    3 10 N

    I looked at the usage of NOT IN but was not quite sure as to how to accomplish the above result. Any help would be greatly appreciated.

    Apologies for the poor choice of the title.

    Thank you,
    Sankar.
    Sankar B. Mandalika

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You can generate a list of number from 1 to 10 with the code
    Code:
    Select rownum num
    from dual
    connect by 1=1 and level <= 10
    and join this to the list of serial numbers already in the table, then subtract all of the existing combinations of id and serial. Something like this ought to work ...
    Code:
    insert into result
    Select id,
           serial,
           'N'
    From
           (
           Select id,
                  serial
           From   (Select distinct id
                   from       result),
                  (Select     rownum   serial
                   from       dual
                   connect by 1=1 and
                              level <= 10)
           Minus
           Select Distinct
                  id,
                  serial
           From   result
           )
    /
    Haven't tested it though
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2003
    Posts
    31
    This works great SlimDave! Thanks very much.
    Sankar B. Mandalika

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width