在一个查询中选择多对多表 [英] SELECT many to many tables in one query

问题描述

我有桌子:

**RUBRIC:** `RubricID, RubricName`

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

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

**BOOKAUTOR:** `BookID, AutorID`

**BOOKRUBRIC:** `BookID, RubricID`

我想在一个SELECT查询中编写一个返回BookName，FirstName，LastName，RubricName的查询.

I want to write in one SELECT query that return BookName, FirstName, LastName, RubricName.

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

I have tow separate query but I do not know how to write this in one select. This is what I got:

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;

效果很好，但是如何一次选择书写呢?

It works good but how to write in one select?

我尝试了一下，但是没用:

I try this but it doesn't work:

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'

推荐答案

尝试此查询

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;

这篇关于在一个查询中选择多对多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！