MySQL根据同一列的两个不同值查找结果

Question

列出同时拥有 49008 邮政编码客户和 49009 邮政编码客户的员工姓名 (Ename)。

我正在努力根据上面的表格回答上述查询。

如果表之间的名称匹配，则假定存在约束。

我可以通过左连接和分组轻松过滤到名称和压缩，但之后会很困难，我不知道正确的位置或具有声明。我假设它可以通过子查询做得更好，但不确定。理想情况下，单个查询。

请并谢谢您。

1) 创建并插入示例数据的语句:

Create table Employees (EM_Eno INT NOT NULL, EM_Ename VARCHAR(50), EM_Hire_Date DATE, PRIMARY KEY(EM_Eno));

Create table Customers (Customers_Cno INT NOT NULL, Customers_Cname VARCHAR(50), Customers_Street VARCHAR(50), Customers_Zip INT, Customers_Phone INT, primary key(Customers_Cno)); 

Create table Orders (Orders_Ono INT NOT NULL, Orders_Cno INT, Orders_Eno INT, Orders_Received DATE, Orders_Shipped DATE, primary key(Orders_Ono));


insert into Orders values
( 1,301,501,20161010,20161011);
( 2,302,501,20161011,20161012);
( 3,303,502,20161110,20161111);
( 4,304,502,20161110,20161112);
( 5,305,502,20161110,20161113);
( 6,306,503,20161112,20161114);
( 7,307,501,20161112,20161113);
( 8,308,503,20161112,20161115);
( 9,309,503,20161115,20161120);
(10,300,501,20161112,20161113);

insert into Customers values
(300,'Bryan','100 street',49009,1234567890),
(301,'Ryan','101 street',49008,1234567890),
(302,'Nick','102 street',49009,1234567890),
(303,'Nicholas','103 street',49009,1234567890),
(304,'Alexa','104 street',49009,1234567890),
(305,'Tori','105 street',49008,1234567890),
(306,'Scarlet','106 street',49008,1234567890),
(307,'Heather','100 street',49009,1234567890),
(308,'Amanda','107 street',49008,1234567890),
(309,'James','108 street',49008,1234567890);

insert into Employees values
(501,'Robert',20041010),
(502,'Sam',20050110),
(503,'Brandy',20050710);

2) 理想的最终结果是回答查询“列出同时拥有 49008 邮政编码客户和 49009 邮政编码客户的员工(姓名)。”

3)迄今为止的最佳尝试:

select Employees.EM_Ename
     , Customers.Customers_Zip 
  from Employees 
  left 
  join Orders 
    on Employees.EM_Eno = Orders.Orders_Eno 
  left 
  join Customers 
    on Orders.Orders_Cno = Customers.Customers_Cno 
 group 
    by Employees.EM_Ename
     , Customers.Customers_Zip;

Answer 1

表名称在 rexttest 中略有更改，因为其他表已存在于其中具有相同的名称...

SELECT e.*
  FROM tbl_employees e
  JOIN tbl_orders o
    ON o.orders_eno = e.em_eno
  JOIN tbl_customers c
    ON c.Customers_Cno = o.Orders_Cno
 WHERE c.Customers_Zip IN(49008,49009)
 GROUP
    BY e.em_eno
HAVING COUNT(DISTINCT customers_zip) = 2;

http://rextester.com/HCNLU51847