MySQL query - multiple JOIN

Question

I have a database sort of like a dictionary. Essentially, there are six tables:

words_a (id_a, word_a) contains all words of language A

words_b (id_b, word_b) contains all words of language B

words (id, id_a, id_b) connects words of language A and their corresponding translation to language B

categories (id, cat_id) assigns categories to every worda-wordb-relationship from words-table

Now what I want to do is get all translations in a certain category.

I choose a category id (cat_id) and get all the word-word-relationships (id) in that category, then I get the two corresponding word IDs (id_a and id_b) for those relationships:

    SELECT id_a, id_b
    FROM words, (
        SELECT *
        FROM `categories`
        WHERE cat_id =6
    ) AS temp
    WHERE words.id = temp.id

This gives me a table with the IDs of all words in language A and the IDs of their corresponding translations to language B in category 6. Now I need the actual words to the IDs.

SELECT word_a, word_b FROM words_a,words_b, (
    SELECT id_a,id_b 
    FROM words, (
        SELECT * 
        FROM categories 
        WHERE cat_id=6 ) AS t 
    WHERE words.id = t.id ) AS temp 
WHERE ??? )

What should my where-clause look like?

JeSuisse · Accepted Answer · 2013-09-07 10:20:59Z

1

How about

SELECT word_a, word_b FROM words_a,words_b,word,categories WHERE words.id = categories.id and word_a.id_a = words.id_a and word_b.id_b = words.id_b AND categories.cat_id = 6

This creates a join over all 4 tables instead of using subqueries.

answered Sep 7, 2013 at 10:20

JeSuisse

3591 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query - multiple JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related