How can I select data with one query?

Question

I have the following tables:

Table xwrh9_betty_dictionary_ids

+----+----------+-------+-------------+---------------------+-------+-------+
| id | ordering | state | checked_out | checked_out_time    | lang1 | lang2 |
+----+----------+-------+-------------+---------------------+-------+-------+
|  3 |        0 |     1 |           0 | 0000-00-00 00:00:00 |    18 |    19 |
|  4 |        0 |     1 |           0 | 0000-00-00 00:00:00 |    18 |    20 |
|  5 |        0 |     1 |           0 | 0000-00-00 00:00:00 |    18 |    21 |
|  6 |        0 |     1 |           0 | 0000-00-00 00:00:00 |    18 |    22 |
+----+----------+-------+-------------+---------------------+-------+-------+

Table xwrh9_betty_dictionary_words

+----+----------+-----------+-------+--------+----------------+
| id | language | word      | state | domein | characteristic |
+----+----------+-----------+-------+--------+----------------+
| 18 | en       | sad       |     1 | sad    | sad            |
| 19 | en       | sub       |     1 | am     | al             |
| 20 | hu       | sub2      |     1 | wed    | wed            |
| 21 | ro       | sub3      |     1 | sd     | sd             |
| 22 | en       | sds       |     1 | ss     | sd             |
| 23 | en       | alamkorta |     1 | sd     | sd             |
+----+----------+-----------+-------+--------+----------------+

I have the following query, is it possible to optimize?

SELECT b.id, b.language, b.word, b.state, b.domein, b.characteristic, c.lang1 
FROM xwrh9_betty_dictionary_ids as a  
   INNER JOIN ( SELECT lang1 FROM xwrh9_betty_dictionary_ids WHERE lang2=19) as c  
   LEFT JOIN xwrh9_betty_dictionary_words as b 
      ON ((a.lang2=b.id or b.id=c.lang1) AND b.id<>19)  
WHERE a.lang1=c.lang1 GROUP BY id;

The output is the following:

+------+----------+------+-------+--------+----------------+-------+
| id   | language | word | state | domein | characteristic | lang1 |
+------+----------+------+-------+--------+----------------+-------+
|   18 | en       | sad  |     1 | sad    | sad            |    18 |
|   20 | hu       | sub2 |     1 | wed    | wed            |    18 |
|   21 | ro       | sub3 |     1 | sd     | sd             |    18 |
|   22 | en       | sds  |     1 | ss     | sd             |    18 |
+------+----------+------+-------+--------+----------------+-------+

Answer 1

I don't know mysql syntax, but it seems like the following would give you the same results.

SELECT b.id, b.language, b.word, b.state, b.domein, b.characteristic, a.lang1 
FROM xwrh9_betty_dictionary_ids a 
LEFT JOIN xwrh9_betty_dictionary_words b ON b.id=a.lang1 OR b.id=a.lang2
WHERE b.id<>19
GROUP BY b.id;