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.

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

This works great SlimDave! Thanks very much.