MySQL 查询不会返回预期结果

Question

考虑以下数据集: Tables

架构定义如下:

Episodes (season (PK), num (PK), title, director, viewers)
Characters (name (PK), house)
Appearances (name (PK), season (PK), num (PK))
FK: Appearances.name -> Characters.name
FK: Appearances.season -> Episodes.season 

我正在尝试构建以下查询:

SELECT DISTINCT Episodes.title
FROM Episodes, Appearances
WHERE Episodes.director = 'Van Patten' AND Appearances.name != 'Robb';

然而，当我只期望得到The Kingsroad时，我得到的结果是Winter is Coming和The Kingsroad

我在这里做错了什么？

编辑:这是我刚刚尝试进行连接的方法，但它仍然给我相同的结果。

SELECT DISTINCT Episodes.title
FROM Episodes, Appearances, Characters
WHERE 
Episodes.season = Appearances.season AND
Episodes.num = Appearances.num AND
Characters.name = Appearances.name AND
Episodes.director = 'Van Patten' AND 
Characters.name != 'Robb';

我已经提供了表格的创建以及下面的插入。

CREATE TABLE Episodes (
    season INT,
    num INT,
    title VARCHAR(50),
    director VARCHAR(50),
    viewers DECIMAL(2, 1),
    PRIMARY KEY (season, num)
);

CREATE TABLE Characters (
    name VARCHAR(50),
    house VARCHAR(50),
    PRIMARY KEY (name)
);

CREATE TABLE Appearances (
    name VARCHAR(50),
    season INT,
    num INT,
    PRIMARY KEY (name, season, num),
    FOREIGN KEY (name) REFERENCES Characters(name),
    FOREIGN KEY (season) REFERENCES Episodes(season)
);


# Episodes Table Inserts

INSERT INTO Episodes VALUES (1, 1, 'Winter is Coming', 'Van Patten', 2.2);
INSERT INTO Episodes VALUES (1, 2, 'The Kingsroad', 'Van Patten', 2.2);
INSERT INTO Episodes VALUES (1, 3, 'Lord Snow', 'Kirk', 2.4);
INSERT INTO Episodes VALUES (1, 4, 'Cripples, Bastards', 'Kirk', 2.4);
INSERT INTO Episodes VALUES (1, 5, 'The Wolf & the Lion', 'Kirk', 2.6);
INSERT INTO Episodes VALUES (2, 1, 'The North Remembers', 'Taylor', 3.9);
INSERT INTO Episodes VALUES (2, 2, 'The Night Lands', 'Taylor', 3.8);
INSERT INTO Episodes VALUES (2, 3, 'What is Dead May Never Die', 'Sakharov', 3.8);
INSERT INTO Episodes VALUES (2, 4, 'Garden of Bones', 'Petrarca', 3.7);
INSERT INTO Episodes VALUES (2, 5, 'The Ghost of Harrenhal', 'Petrarca', 3.9);


# Characters Table Inserts

INSERT INTO Characters VALUES ('Eddard', 'Stark');
INSERT INTO Characters VALUES ('Robb', 'Stark');
INSERT INTO Characters VALUES ('John Snow', 'Stark');
INSERT INTO Characters VALUES ('Ygritte', NULL);
INSERT INTO Characters VALUES ('Tyrion', 'Lannister');
INSERT INTO Characters VALUES ('Cercei', 'Lannister');
INSERT INTO Characters VALUES ('Jaime', 'Lannister');
INSERT INTO Characters VALUES ('Daenerys', 'Targaryen');


# Appearances Table Inserts

INSERT INTO Appearances VALUES ('Eddard', 1, 1);
INSERT INTO Appearances VALUES ('Eddard', 1, 2);
INSERT INTO Appearances VALUES ('Eddard', 1, 3);
INSERT INTO Appearances VALUES ('Eddard', 1, 4);
INSERT INTO Appearances VALUES ('Eddard', 1, 5);

INSERT INTO Appearances VALUES ('Robb', 1, 1);
INSERT INTO Appearances VALUES ('Robb', 1, 3);
INSERT INTO Appearances VALUES ('Robb', 2, 1);
INSERT INTO Appearances VALUES ('Robb', 2, 3);

INSERT INTO Appearances VALUES ('John Snow', 1, 1);
INSERT INTO Appearances VALUES ('John Snow', 1, 2);
INSERT INTO Appearances VALUES ('John Snow', 2, 3);
INSERT INTO Appearances VALUES ('John Snow', 2, 4);
INSERT INTO Appearances VALUES ('John Snow', 2, 5);

INSERT INTO Appearances VALUES ('Ygritte', 2, 4);
INSERT INTO Appearances VALUES ('Ygritte', 2, 5);

INSERT INTO Appearances VALUES ('Tyrion', 1, 1);
INSERT INTO Appearances VALUES ('Tyrion', 1, 2);

INSERT INTO Appearances VALUES ('Cercei', 1, 1);
INSERT INTO Appearances VALUES ('Cercei', 2, 3);

INSERT INTO Appearances VALUES ('Jaime', 2, 3);
INSERT INTO Appearances VALUES ('Jaime', 2, 4);

INSERT INTO Appearances VALUES ('Daenerys', 1, 1);

Answer 1

每一集中都会出现多个角色。
因此通过设置条件:

Characters.name != 'Robb'

这不会从返回的行中删除该剧集，因为该剧集还有其他角色的剩余行。
您必须连接(通过使用正确的连接和别名)表，按标题分组，并在 WHERE 子句中设置第一个条件，在 HAVING< 中设置第二个条件 子句:

SELECT e.title
FROM Episodes e
INNER JOIN Appearances a ON a.season = e.season AND a.num = e.num
INNER JOIN Characters c ON c.name = a.name
WHERE e.director = 'Van Patten'
GROUP BY e.title
HAVING SUM(c.name = 'Robb') = 0;

请参阅demo .
结果:

| title         |
| ------------- |
| The Kingsroad |