Left Join Where子句(名称与另一个表中的ID匹配)

Question

我希望输出是员工ID为1的David服务过的客户的生日、名字和姓氏。但由于某种原因(很明显，我只是一个初学者(，它只输出一行Victor的c_id与e_id匹配(我知道线索就在那里，但不知道(。

我该如何让它发挥作用？

CREATE TABLE customers (
birth_day date,
first_name VARCHAR(20),
last_name VARCHAR(20),
c_id int,
CONSTRAINT PK_Customers PRIMARY KEY (c_id));
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1993-07-11','Victor','Davis',1);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('2001-03-28','Katarina','Williams',2);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1965-12-11','David','Jones',3);
INSERT INTO customers (birth_day, first_name, last_name, c_id) VALUES ('1980-10-10','Evelyn','Lee',4);

CREATE TABLE employees (
birth_day date,
first_name VARCHAR(20),
last_name VARCHAR(20),
e_id int,
CONSTRAINT PK_Employees PRIMARY KEY (e_id)
);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1983-09-02','David','Smith',1);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1990-07-23','Olivia','Brown',2);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1973-05-11','David','Johnson',3);
INSERT INTO employees (birth_day, first_name, last_name, e_id) VALUES ('1999-11-21','Mia','Taylor',4);
CREATE TABLE transactions (
e_id int,
c_id int,
date date,
t_id int,
CONSTRAINT PK_transactions PRIMARY KEY (t_id),
FOREIGN KEY (e_id) REFERENCES employees(e_id),
FOREIGN KEY (c_id) REFERENCES customers(c_id)
);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,1,'2020-8-11',1);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (3,1,'2020-8-15',2);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (1,4,'2020-9-01',3);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (2,2,'2020-9-07',4);
INSERT INTO transactions (e_id, c_id, date, t_id) VALUES (4,3,'2020-9-07',5);
CREATE VIEW DavidSoldTo AS 
SELECT DISTINCT birth_day, first_name, last_name
FROM customers
LEFT JOIN transactions on customers.c_id = transactions.e_id 
WHERE e_id = '1'
ORDER  BY birth_day
;

Answer 1

EXISTS是更好的表达方式：

CREATE VIEW DavidSoldTo AS 
SELECT c.birth_day, c.first_name, c.last_name
FROM customers c 
WHERE EXISTS (SELECT 1
FROM transactions t
WHERE t.c_id = c.c_id AND t.e_id = 1
)
ORDER BY c.birth_day ;

注：

• 使用c_id，JOIN条件现在是正确的(尽管在此版本中，条件位于相关子句中(
• 您不需要SELECT DISTINCT，因为当"；David"；为某人服务不止一次。这是一场巨大的表演胜利
• e_id是一个数字，所以比较应该是数字(1(，而不是字符串('1'(