sql - 从具有多个条件的多个表中选择

Question

我有个问题。我有三个表，名为 AUTHORS、BOOKS_AUTHORS 和 BOOKS，我试图从这三个表中只选择 Lev Tolstoi 写的书。这是我现在拥有的，但它不起作用

select   "AUTHORS"."LASTNAME" as "LASTNAME",
     "AUTHORS"."FIRSTNAME" as "FIRSTNAME",
     "BOOKS"."NAME" as "NAME",
 from    "BOOKS_AUTHORS",
     "BOOKS",
     "AUTHORS" 
 where   "AUTHORS"."ID"="BOOKS_AUTHORS"."ID_AUTHOR"
 and     "BOOKS_AUTHORS"."ID_BOOK"="BOOKS"."ID"
 and     "AUTHORS"."LASTNAME"="Tolstoi" 
 and     "AUTHORS"."FIRSTNAME"="Lev"

我在这个网站上有名为 iacademy3.oracle.com 的表格，错误是

“ORA-00936：缺少表达式”

标签： sql

Answer 1

为什么要使用旧JOIN语法，请使用适当的显式JOIN语法以使其更具可读性并且在需要时易于扩展到其他连接。

select 
       a.FIRSTNAME, a.LASTNAME, b.Name as BookName
from AUTHORS a 
inner join BOOKS_AUTHORS bk on bk.ID_AUTHOR = a.id
inner join BOOKS b on b.id = bk.ID_BOOK
where a.FIRSTNAME = 'Lev' and a.LASTNAME = 'Tolstoi';