如何根据两个不同表之间的关系从一个表中选择所有结果?

vecaoik1  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(278)

我有三个表:inquery、operator和operator\u region。

表中输入的数据如下:

每个操作符可以有一个或多个区域。我需要显示查询表中区域字段等于登录用户(operator)operator\u region.regionname字段的所有结果。见下图:

我尝试如下:

SELECT region FROM `INQUIRY`
INNER JOIN `OPERATOR` on `INQUIRY`.`region` WHERE `INQUIRY`.`region` IN (

SELECT
    regionName
FROM
    `OperatorRegion`
INNER JOIN `Operator` ON `OperatorRegion`.`OperatorID` = operatorID
WHERE
    operatorRegion.operatorID = 2)

我需要查询只返回与登录操作员的区域匹配的查询表行(在本例中是alisson)。运算符区域位于“运算符区域表”中。
但却空空如也。这是我的小提琴

lnlaulya

lnlaulya1#

考虑以下几点:

DROP TABLE IF EXISTS inquiry;

CREATE TABLE inquiry
(inquiry_id SERIAL PRIMARY KEY
,region VARCHAR(12) 
);

INSERT INTO inquiry VALUES
(1,'Hawaii'),
(2,'Hawaii'),
(3,'Paris'),
(4,'New York');

DROP TABLE IF EXISTS operator;

CREATE TABLE operator 
(operator_id SERIAL PRIMARY KEY
,operator_name VARCHAR(12) NOT NULL UNIQUE
);

INSERT INTO operator VALUES
(101,'John'),
(102,'Alisson'),
(103,'Peter');

DROP TABLE IF EXISTS operator_region;

CREATE TABLE operator_region 
(operator_id INT NOT NULL
,region VARCHAR(12) NOT NULL
,PRIMARY KEY (operator_id,region)
);

INSERT INTO operator_region (operator_id,region) 
VALUES (102,'New York'),
       (102,'Los Angeles'),
       (102,'Hawaii'),
       (101,'New York'),
       (103,'Paris');

SELECT i.* 
  FROM inquiry i 
  JOIN operator_region ro 
    ON ro.region = i.region 
  JOIN operator o 
    ON o.operator_id = ro.operator_id 
 WHERE o.operator_name = 'Alisson';
+------------+----------+
| inquiry_id | region   |
+------------+----------+
|          1 | Hawaii   |
|          2 | Hawaii   |
|          4 | New York |
+------------+----------+

相关问题