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.