从3个表中选择，其中包含子句

Question

需要显示下一步：来自表用户名的所有fname和lname具有" admin"的位置，并在'abc'Company(name_company(中工作，并在子句中。

。

create table company 
(
    CODE_COMPANY char(30),
    NAME_COMPANY varchar2(30) not null,
    MAIL_COMPANY varchar2(30) null,
    constraint PK_CODE_COMPANY primary key (CODE_COMPANY),
);
create table USERNAME 
(
    NAME_USERNAME varchar2(30),
    USER_LOCATION number,
    fNAME varchar2 (30) not null,
    lNAME varchar2 (30) not null,
    PHONE_USER char(13) null,
    USER_POSITION varchar2 (30),
    check (USER_POSITION in('Admin', 'Superadmin', 'Technician', 'Student')),
    constraint PK_NAME_USERNAME primary key (NAME_USERNAME),
    constraint FK_USER_LOCATION foreign key (USER_LOCATION) references uLOCATION (LOCATION)
);
create table uLOCATION 
(
    LOCATION number,
    CODE_COMPANY char(30),
    NAME_LOCATION varchar2(30) not null,
    FLOOR_LOCATION varchar2(10),
    check (FLOOR_LOCATION in ('MAIN_FLOOR', '1ST FLOOR', '2ND FLOOR', '3RD FLOOR')),
    constraint PK_LOCATION primary key (LOCATION),
    constraint FK_CODE_COMPANY_L foreign key (CODE_COMPANY) references company (CODE_COMPANY),
);

Answer 1

如果我正确理解您的问题，则需要以下查询。不确定为什么您会使用IN代替标准=，但是我两者都将其包含在一个评论中。

select
    user.fName,
    user.lName
from
    username as user
    inner join ulocation as location
        on location.location = user.user_location
    inner join company as company
        on company.code_company = location.code_company
where
    user.user_position = 'Admin'
    and name_company in ('A','B','C') -- not needed if only checking for one company
                                      -- if only one company, change to: name_company = 'ABC'

Answer 2

我认为您正在寻找：

select u.*
from username u
where u.user_position = 'admin' and
      u.ulocation in (select l.location
                      from ulocation l join
                           ucompany c
                           on l.code_company = c.code_company
                      where c.name_company = 'ABC'
                     );