-
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
-
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|