-1

I have MySql database with 3 tables:

language
lang_id       lang

word
word_id      lang_id       word

translation
trans_id      word_id1    word_id2

And I want to get all translations between two languages I choose. Following command (with a wrong syntax) is based on:
Two foreign keys of the same table. How do I write this SELECT statement?
MySQL multiple Inner Join with Where clause

In the command I'm trying to get translations between languages 1 and 2.

SELECT w1.word, lang1.lang AS w2.word, lang2.lang AS FULL
    FROM translation AS trans
    (JOIN (word AS w1 JOIN language AS lang1 ON lang1.id_lang=w1.lang_id)
       ON trans.word_id1=w1.id_word WHERE (w1.lang_id=1 OR w1.lang_id=2))
    (JOIN (word AS w2 JOIN language AS lang2 ON lang2.id_lang=w2.lang_id)
       ON trans.word_id2=w2.id_word WHERE (w2.lang_id=1 OR w2.lang_id=2))

SOLUTION Thanks to @ahmed-gdoma I came up with this command:

SELECT w1.word AS w1, l1.lang AS L1,w2.word AS w2, l2.lang AS L2 FROM translation AS t 
JOIN word AS w1 on w1.id_word=t.word_id1 
JOIN word AS w2 on w2.id_word=t.word_id2
JOIN language AS L1 on w1.lang_id=L1.id_lang
JOIN language AS L2 on w2.lang_id=L2.id_lang
WHERE (l2.id_lang=1 OR l2.id_lang=2) 
AND (l1.id_lang=2 OR l1.id_lang=1)

It returns word (w1), its language (L1), translation of the word (w2) and language of the translation (L2)

Petr Kraus
  • 1
  • 1
  • Duplicate of [How to get matching data from another SQL table for two different columns: Inner Join and/or Union?](https://stackoverflow.com/q/27682228/3404097) – philipxy Dec 22 '17 at 03:25
  • You give two unclear descriptions, "all translations between two specific languages" and "get translations between languages 1 and 2" and you give a query that doesn't do that. How are we supposed to know what you want? Give example DDL, input parameters & tables and desired output and use the English language to explain what you want in a way that does not rely on the example. Please read & act on [mcve]. PS [PKs, FKs & other constraints are not needed to query.](https://stackoverflow.com/a/23842061/3404097) – philipxy Dec 22 '17 at 09:00
  • Hi. What does "not working" mean? That query doesn't even parse. Read the syntax for SELECT statements in the manual or even any intro. Your innermost parentheses are unnecessary & your others are grouping illegally. A subquery (ie a select where a table name can go) requires parentheses and an alias. But you don't *need* subqueries because you can do a series of joins. Build your query from parts that you get to work separately. – philipxy Dec 22 '17 at 09:03

2 Answers2

1
SELECT w1.word AS L1,w2.word as L2 FROM translation AS t 
JOIN word AS w1 on w1.word_id=t.word_id1 
JOIN word AS w2 on w2.word_id=t.word_id2
ahmed gdoma
  • 21
  • 3
  • Thank you, even this is not what I was asking for, it led to the right command. Your command returns only words, I wanted words + languages. Here is what I came up with: 'SELECT w1.word AS w1, l1.lang AS L1,w2.word AS W2, l2.lang AS L2 FROM translation AS t JOIN word AS w1 on w1.id_word=t.word_id1 JOIN word AS w2 on w2.id_word=t.word_id2 JOIN language AS L1 on w1.lang_id=L1.id_lang JOIN language AS L2 on w2.lang_id=L2.id_lang' – Petr Kraus Dec 22 '17 at 09:42
0

The problem is that FULL is a reserved keyword in MySQL, and as such, you need to pick a different name for your AS.

In the following, I replace this with COMBINED:

SELECT w1.word, lang1.lang AS w2.word, lang2.lang AS COMBINED
FROM translation AS trans
(JOIN (word AS w1 JOIN language AS lang1 ON lang1.id_lang=w1.lang_id)
   ON trans.word_id1=w1.id_word WHERE (w1.lang_id=1 OR w1.lang_id=2))
(JOIN (word AS w2 JOIN language AS lang2 ON lang2.id_lang=w2.lang_id)
   ON trans.word_id2=w2.id_word WHERE (w2.lang_id=1 OR w2.lang_id=2)

Hope this helps! :)

Obsidian Age
  • 41,205
  • 10
  • 48
  • 71