Do not include in the LoV the car numbers already used in the second table

AndriiPrincip · Sep 7, 2019 4:27AM

I have two tables, in one table “Car number”, I have a list of car numbers there. In the second table “Registered numbers” I have registered car numbers there. In the “Registered Numbers” table, I fill out the form using the LOV list taken from the “Number Table” table. Is it possible to make sure that in the form when filling out "Registered numbers" in the LOV list there are only those numbers that have not been registered before. Since now all the numbers from the “Car number” table appear in the list. Thanks for the help)

CREATE TABLE number_car id NUMBER(10) , number varcahar2(10), date_add varcahar2(30) ); CREATE TABLE registered_numbers id NUMBER(10) , reg_number varcahar2(10), date_reg varcahar2(30) ); select for form table registered_numbers (LoV); select number as num,number from number_car

Pavel_p · Sep 7, 2019 10:05AM

Hi,

number is a reserved word, so either you have to enclose the column name with double-quotes or (more preferably) give it some other name like CAR_NUMBER (and assuming that your columns are varchar2, not varcahar2 you can create a LOV like:

select n.car_number d, n.id from  number_car n where not exists(select 1 from registered_numbers r where n.id = r.id);--orselect car_number d, id r from number_car where id not in(select id from registered_numbers);

May I ask why you have 2 tables? Is the relation between tables 1:1 or 1:n (i.e. there can be more records for the same id in REGISTERED_NUMBERS table)?

Regards,

Pavel

Ora_Learner890 · Sep 8, 2019 1:31AM

in addition to @Pavel solution, one another way using MINUS as below with appropriate WHERE clause to not mess-up multiple entries found in Second_Table as @Pavel already mentioned for.

select car_number from table_a MINUS select car_number from second_table

hope you already have done as per Pavel guide you

Forum Stats

Quick Links

Categories

Do not include in the LoV the car numbers already used in the second table

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Do not include in the LoV the car numbers already used in the second table

Answers