SQL Query : Help with usage of NOT IN

[sankar6254]

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.

[slimdave]

You can generate a list of number from 1 to 10 with the 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 ...

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

[sankar6254]

This works great SlimDave! Thanks very much.