Left Join Where Clause (name matching ID from another table)

I want to the output to be the birthday, first and last name of customers who have been served by David who has the employee ID of 1. But for some reason (and it may be obvious, im only a beginner), it only outputs one row of Victor who’s c_id matches e_id (which I know the clue is somewhere there but can’t figure out).

How do I get this to work?

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

EXISTS is a much better way to express this:

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 ;

Notes:

The JOIN conditions are now correct, using c_id (although in this version, the conditions are in a correlation clause.
You do not need SELECT DISTINCT, because no duplicates are created when “David” has served someone more than once. This is a big performance win.
e_id is a number, so the comparison should be to a number (1) not to a string ('1').

Advertisement

Answer