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

心理教皇

我希望输出为由戴维服务的客户的生日，名字和姓氏，该员工的员工ID为1。但是由于某种原因（很明显，我只是一个初学者），它只会输出一排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
;

戈登·利诺夫

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 ;

笔记：