MySQL JOIN 全选

Question

我有下表:

书

book_id | book_name | book_photo

标题栏

rubric_id | rubric_name

作者

author_id | first_name | last_name

BOOK_RUBRIK

book_id | rubric_id

BOOK_AUTHOR

book_id | author_id

我想像这样使用 JOIN 选择:

SELECT 
  book.book_name, 
  autor.first_name, 
  autor.last_name, 
  rubric_rubric_name 
FROM book 
  INNER JOIN book_autor ON book.book_id = autor_id 
  LEFT JOIN book_rubric ON book.book_rubric = rubric_id 
ORDER BY book.book_id;

但它不起作用。如何选择book_name, rubric_name, first_name and_last_name？

Answer 1

您错过了一张表 authors，因此您无法提取有关作者的数据:

SELECT 
  book.book_name, 
  autor.first_name, 
  autor.last_name, 
  rubric_rubric_name 
FROM book 
  INNER JOIN book_autor ON book.book_id = book_autor.book_id 
  INNER JOIN author ON book_autor.autor_id = autor.autor_id 
  LEFT JOIN book_rubric ON book.book_rubric = rubric_id 
ORDER BY book.book_id;

添加 INNER JOIN book_autor ON book.book_id = book_autor.book_id 和INNER JOIN author ON book_autor.autor_id = autor.autor_id 而不是 INNER JOIN book_autor ON book.book_id = autor_id 将解决问题。

编辑:

我错过了作者脚本中的名字有一些错误的事实。我修复了选择。这是正确的:

您的表结构:

BOOK
  book_id 
  book_name 
  book_photo

RUBRIC
  rubric_id 
  rubric_name

AUTHOR
  author_id 
  first_name 
  last_name

BOOK_RUBRIK
  book_id 
  rubric_id

BOOK_AUTHOR
  book_id 
  author_id

您的选择语句:

SELECT 
  b.book_name, 
  a.first_name, 
  a.last_name, 
  br.rubric_name 
FROM 
  BOOK AS b 
  INNER JOIN BOOK_AUTHOR ba ON b.book_id = ba.book_id 
  INNER JOIN AUTHOR a ON ba.author_id = a.author_id 
  LEFT JOIN BOOK_RUBRIK br ON b.book_id = br.book_id 
ORDER BY 
  b.book_id;