Oracle database I have a worksheet EXT the first field is to perform the task of personnel number field name userId is likely to be more could also be a (for example, more than 001002003 in comma-separated) I have a staff list emp, employees have an employee number in the table empCde, I want to connect the two tables associated query to employee number (select * from emp p, EXT t where p.e mpCde=t.u serId), but the question is more than one can execution task corresponding to not to come out (such as: empCde='001', userId='001002'), could you tell me how to write the federated query, I want to take the departure of the emp table namely valid field value is 0, thank you very much O (studying studying) O thank you!!

CodePudding user response:

Said the table structure, and your expected results, use excel to draw once, look more clearly;

CodePudding user response:

Use regexp_substr () comma-separated list into multiple lines, the formation of the result set after the ext, again with emp federated query result set, regexp_substr () to baidu check about usage

CodePudding user response:

Select * from emp p, ext t where t.u serId like '%' | | p.e mpCde | | '%'

CodePudding user response:

The create table ext (userid varchar2 (32), worktype varchar2 (32));
The create table emp (empcde varchar2 (32), workername varchar2 (32));

Insert into ext (userid, worktype) values (' 001002 ', '1');
commit;
Insert into ext (userid, worktype) values (' 001 ', '2');
commit;
Insert into emp (empcde workername) values (' 001 ', 'zhang');
commit;
Insert into emp (empcde workername) values (' 002 ', 'bill');
commit;
/*
This approach first won't appear the following situation
Employees a 001 employees b 1001 a and b duplicate
*/
Select * from ext a, emp b where instr (a.u serid, b.e mpcde) & gt;=1

/* you say this kind of question one can have multiple employees work can also be composed of multiple employees do
The best three tables to handle
A staff table a worksheet with a relational table can better handle the many-to-many relationship */

CodePudding user response:

Positive solution to second floor