SQLite How do i join two tables

Question

I have two tables that contains translations of different words in english and french

words : contains all the words used in the app

+----+-------+---------+--
| ID | TEXT    | LANG_ID |       
+----+-------+---------+--
|  1 | partir  | 4       |
|  2 | manger  | 4       | 
|  3 | go      | 5       | 
|  4 | eat     | 5       | 
+----+-------+---------+--

Translated_word : contains the translations (english to french and vice versa)

+----+-------+---------+--
| ID | SOURCE   | TO      |       
+----+-------+---------+--
|  10 | 1       | 3       |
|  12 | 2       | 4       | 
|  13 | 3       | 1       | 
|  14 | 4       | 2       | 
+----+-------+---------+--

I need to get all the contents of the word table in this format

1 partir, go 
2 manger, eat
3 go, partir
4 eat, manger 

I have tried several queries and i am not able to achieve the desired results. For example, when i try this one

SELECT lp.id, tw.id, lp.text, tw.text FROM words AS lp JOIN words AS tw ON (tw.id = lp.id)

i get the following results

1 partir, partir
2 manger, manger
...
...

Can someone let me know what i am doing wrong ?

Thanks

Answer 1

Join translated_word to 2 copies of words:

select w1.id,
       w1.text || ', ' || w2.text 
from words w1
inner join translated_word tw on tw.source = w1.id
inner join words w2 on w2.id = tw.`to`

See the demo.

Answer 2

This would use two joins:

select tw.id, w1.*, w2.*
from translated_word tw join
     words w1
     on tw.source = w1.id join
     words w2
     on tw.to = w2.id and w2.lang_id <> w1.lang_id     
where lang_id in (4, 5);

Note the additional condition on the second join . . . the language ids are not equal. I might expect that you want English and French in different columns. For that:

select tw.id, w1.*, w2.*
from translated_word tw join
     words wf
     on tw.source = wf.id and wf.lang_id = 4 join
     words we
     on tw.to = we.id and we.lang_id = 5    
where lang_id in (4, 5);