小编典典

在一个查询中选择多对多表

sql

我有桌子:

**RUBRIC:** `RubricID, RubricName`

**AUTOR:** `AutorID, FirstName, LastName`

**BOOK:** `BookID, BookName, book_photo`

**BOOKAUTOR:** `BookID, AutorID`

**BOOKRUBRIC:** `BookID, RubricID`

我想写一个返回BookName,FirstName,LastName,RubricName的SELECT查询。

我有两个单独的查询,但我不知道如何在一个选择中编写此查询。这就是我得到的:

SELECT book.BookName, autor.FirstName, autor.LastName FROM bookautor JOIN book ON book.BookID=bookautor.BookID JOIN autor on autor.AutorID=bookautor.AutorID;

SELECT rubric.RubricName FROM bookrubric JOIN rubric ON rubric.RubricID=bookrubric.RubricID;

它工作正常,但如何在一个选择中编写?

我尝试这样做,但是不起作用:

SELECT book.BookName, autor.FirstName, autor.LastName, rubric.RubricName FROM bookautor,bookrubric JOIN book ON book.BookID=bookautor.BookID JOIN autor on autor.AutorID=bookautor.AutorID LEFT JOIN rubric ON rubric.RubricID=bookrubric.RubricID;

它返回错误:

ERROR 1054 (42S22): Unknown column 'bookautor.BookID' in 'on clause'

阅读 174

收藏
2021-04-14

共1个答案

小编典典

试试这个查询

SELECT book.BookName, autor.FirstName, autor.LastName, rubric.RubricName 
FROM bookautor
JOIN book ON book.BookID=bookautor.BookID 
JOIN autor on autor.AutorID=bookautor.AutorID 
left join bookrubric on book.BookID=bookrubric.BookID
LEFT JOIN rubric ON rubric.RubricID=bookrubric.RubricID;
2021-04-14