CODEWITHSUNDEEP
mysqlsql
fabrizio_ff
fabrizio_ff

Reputation: 65

Joining four tables with MySQL and get values from two of them

I looked to similar questions but I did not get to find the right syntax.

I have these tables:

Quotes - quotes_id - author_id - quote_ENG

Authors - author_id - author

TopicMap - topicmap_id - topics_ENG

QuotesByTopic - quotes_id - topicmap_id

I need to get 'quote_ENG' from table Quotes and 'author' from table Authors WHERE 'topic_ENG'='age'.

I am able to get the 'quote_ENG' value...: 

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

...but I miss the last passage to get also the 'author' value:

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

Thank you for any help

Upvotes: 1

Views: 64

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this instead:

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'

Upvotes: 3

benisntfunny
benisntfunny

Reputation: 103

EDIT: you changed your question as I was typing... I'd still use a distinct incase you want to do something like tma.topics_ENG IN ('age','gender'). As the other person showed I would use explicit joins. A lot easier for everyone, including yourself, to read and understand.

SELECT DISTINCT
       ath.author,
       qts.quote_ENG
FROM quotes qts
INNER JOIN Authors ath
    ON qts.author_id = ath.author_id
INNER JOIN QuotesByTopic qbt
    ON qts.quote_id = qbt.quote_id
INNER JOIN TopicMap tma
    ON qbt.topicmap_id = tma.topicmap_id
WHERE tma.topics_ENG = 'age';

Upvotes: 0

Related Questions