mysql - 加入查询

Question

这周我正在准备考试，我看到了这样一个过去的试卷问题:

讲师

lec_id
lec_name
phone
dept

请求

lec_id
book_id 
qty 
date

书

book_id
title
author
cost
supplier

我需要编写一个 MySQL 命令来查找请求 ID# 123 图书的每位讲师的姓名和电话号码。

我尝试创建如下所示的查询:

SELECT lec_name, phone FROm lecturer 
  LEFT JOIN requests ON requests.lec_id = lecturer.lec_id 
  LEFT JOIN requests ON requests.book_id = book.book_id
WHERE book.book_id = 123

但是我不断收到错误 Not unique table/alias: 'requests'

Answer 1

最好从 book 开始，这样 WHERE 子句更清晰，并且可能更直观地适应于搜索另一本特定书籍和/或所有书籍:

select book.book_id, book.title, lecturer.lec_name, lecturer.phone 
from book
left join requests on book.book_id = requests.book_id
left join Lecturer on requests.lec_id = Lecturer.lec_id 
where book.book_id = 123