MySQL 删除值如果存在于另一行

Question

好的，我有一个问题。

我在作者和书籍之间建立了多对多关系，它创建了一个连接表，我将其命名为 CoverPage。

 Author:
 AuthorID
 AuthorName

 CoverPage: (Junction Table)
 AuthorID
 BookID

 Book:
 BookID
 BookTitle
 BookType

问题:创建一个语句，显示所有写过一本小说书的作者，但不显示任何写过一本历史书的作者。

例如:

AuthorName:     BookName:            Book Type:
John Smith     The Awakening       Fiction
John Smith     WW2                 Historical
Jack Johnson   The last Alien      Fiction 

查询应该只显示 Jack Johnson，因为 John Smith 尽管他创作了一本小说书，但他也创作了一本历史书，但不幸的是，在我的查询中他仍然出现。

类似于:

Select distinct AuthorName
From Author, Book, CoverPage
Where Author.AuthorID=CoverPage.AuthorID 
And Book.BookID=CoverPage.BookID 
And BookType = 'Fiction' And Not BookType = 'Historical';

或者

 SELECT distinct AuthorName, EmailAddress 
 FROM Author
 LEFT JOIN CoverPage ON Author.AuthorID = CoverPage.Author_AuthorID
 LEFT JOIN Book ON CoverPage.Book_BookID = Book.BookID
 Where Not BookType = 'Historical'
 Group By AuthorID;

这是我的表格:

CREATE TABLE IF NOT EXISTS `Publisher` (
`PublisherID` VARCHAR(3) NOT NULL,
`PublisherName` VARCHAR(30) NULL,
`City` VARCHAR(15) NULL,
`ContactNumber` VARCHAR(10) NULL,
 PRIMARY KEY (`PublisherID`))
 ENGINE = InnoDB;

 CREATE TABLE IF NOT EXISTS `Author` (
  `AuthorID` INT NOT NULL,
`AuthorName` VARCHAR(20) NULL,
`EmailAddress` VARCHAR(25) NULL,
 PRIMARY KEY (`AuthorID`))
 ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Book` (
`BookID` INT NOT NULL,
`BookTitle` VARCHAR(30) NULL,
`BookType` VARCHAR(10) NULL,
`Price` FLOAT NULL,
`PaperBack?` VARCHAR(5) NULL,
`Publisher_PublisherID` VARCHAR(3) NOT NULL,
PRIMARY KEY (`BookID`),
INDEX `fk_Book_Publisher1_idx` (`Publisher_PublisherID` ASC),
CONSTRAINT `fk_Book_Publisher1`
FOREIGN KEY (`Publisher_PublisherID`)
REFERENCES `Publisher` (`PublisherID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `CoverPage` (
`Book_BookID` INT NOT NULL,
`Author_AuthorID` INT NOT NULL,
PRIMARY KEY (`Book_BookID`, `Author_AuthorID`),
INDEX `fk_Book_has_Author_Author1_idx` (`Author_AuthorID` ASC),
INDEX `fk_Book_has_Author_Book1_idx` (`Book_BookID` ASC),
CONSTRAINT `fk_Book_has_Author_Book1`
FOREIGN KEY (`Book_BookID`)
REFERENCES `Book` (`BookID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Book_has_Author_Author1`
FOREIGN KEY (`Author_AuthorID`)
REFERENCES `Author` (`AuthorID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

insert into Publisher values ('P11','Eureka Publishing', 'Parramatta', '9981 3909' );
insert into Publisher values ('P12','Lishpub Book Publishing', 'Penrith', '9971 3000' );
insert into Publisher values ('P13','The Publisher Corp', 'Gosford', '9931 2999' );
insert into Publisher values ('P15','Stewart Publishing', 'Bankstown', '9931 7000' );
insert into Publisher values ('P14','George Street Publishing', 'Sydney CBD', '9981 4111' );
insert into Author values (321, 'Barry White', 'b.white@gmail.com');
insert into Author values (322, 'Bill Cosby', 'billc@hotmail.com');
insert into Author values (323, 'George Ryan', 'gryan@yahoo.com');
insert into Author values (324, 'Jon Snow', 'jsnow@yahoo.com');
insert into Book values (4321, 'The Awakening', 'Historical', '20.00', 'yes', 'P11');
insert into Book values (4322, 'Indianna Jones', 'Fiction', '30.00', 'yes', 'P12');
insert into Book values (4324, 'Star Wars', 'Fiction', '20.00', 'yes', 'P14');
insert into Book values (4323, 'The Lost Magic', 'Education', '10.00', 'no', 'P13');
insert into Book values (4325, 'World War', 'Historical', '20.00', 'yes', 'P14');
insert into Book values (4326, 'Doctor No', 'Fiction', '25.00', 'yes', 'P12');
insert into Book values (4327, 'Spiderman', 'Fiction', '10.00', 'no', 'P12');
insert into CoverPage values (4321, 321);
insert into CoverPage values (4321, 323);
insert into CoverPage values (4324, 322);
insert into CoverPage values (4325, 322);
insert into CoverPage values (4324, 323);
insert into CoverPage values (4326, 323);
insert into CoverPage values (4327, 323);
insert into CoverPage values (4327, 324);

Answer 1

考虑 NOT EXISTS 子句:

SELECT a.AuthorName
FROM Author a
INNER JOIN CoverPage c ON a.AuthorID = c.AuthorID 
INNER JOIN Book b ON b.BookID = c.BookID AND b.BookType = 'Fiction'

WHERE NOT EXISTS
 (SELECT 1 FROM Author sub_a 
  INNER JOIN CoverPage sub_c
     ON sub_a.AuthorID = sub_c.AuthorID 
  INNER JOIN Book sub_b
     ON sub_b.BookID = sub_c.BookID AND sub_b.BookType = 'Historical'
  WHERE sub_a.AuthorID = a.AuthorID);