Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
+------+----------+------+-------+--------+----------------+-------+
share|improve this question
5  
Which RDBMS are you using? –  Thomas Stringer Jul 10 '12 at 19:18
    
It isn't valid Oracle syntax. –  Leigh Riffel Jul 10 '12 at 20:55
1  
This syntax is possible only in MySQL that does not distinct between INNER JOIN and CROSS JOIN –  ypercube Jul 10 '12 at 23:24
add comment

1 Answer

up vote 4 down vote accepted

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;
share|improve this answer
    
sqlfiddle.com/#!4/12570/1 –  Leigh Riffel Jul 12 '12 at 15:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.