mysql - 使用 MySQL 连接四个表并从其中两个获取值

Question

我查看了类似的问题，但没有找到正确的语法。

我有这些表:

引语- quotes_id- author_id- quote_ENG

作者- author_id——作者

主题图- topicmap_id- topics_ENG

按主题报价- quotes_id- topicmap_id

我需要从 Quotes 表中获取“quote_ENG”，从 Authors 表中获取“author”，其中“topic_ENG”=“age”。

我能够获得“quote_ENG”值...:

SELECT quote_ENG
FROM Quotes, TopicMap, QuotesByTopic
WHERE TopicMap.topics_ENG='age'
  and QuotesByTopic.topicmap_id = TopicMap.topicmap_id
  and QuotesByTopic.quotes_id = Quotes.quotes_id 

...但我错过了最后一段以获取“作者”值:

SELECT author, quote_ENG
FROM Authors, Quotes, TopicMap, QuotesByTopic
WHERE TopicMap.topics_ENG = 'age'
  and Authors.author_id = Quotes.author_id
  and QuotesByTopic.topicmap_id = TopicMap.topicmap_id
  and QuotesByTopic.quotes_id = Quotes.quotes_id

谢谢你的帮助

Answer 1

试试这个:

SELECT 
  q.quote_ENG,
  a.author
FROM QuotesByTopic  AS qt 
INNER JOIN TopicMap AS t ON qt.topicmap_id = t.topicmap_id
INNER JOIN Quotes   AS q ON qt.quotes_id   = q.quotes_id
INNER JOIN Authors  AS a ON a.author_id    = q.author_id
WHERE t.topics_ENG = 'age'