Need query help on translation table problem

[kmillecam]

Hi guys,

I basically have two tables: locales_source (which includes English source phrases) and locales_target (which includes all the translated phrases).

I'm looking for a way to list the English phrases (from locales_source) only if translations in de, fr, and es exist in locales_target.

Any help would be appreciated.

Here's some table info:

mysql> describe locales_source;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| lid       | int(11)      | NO   | PRI | NULL    | auto_increment |
| location  | varchar(255) | NO   |     |         |                |
| textgroup | varchar(255) | NO   | MUL | default |                |
| source    | blob         | NO   | MUL | NULL    |                |
| version   | varchar(20)  | NO   | MUL | none    |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
 
mysql> SELECT * FROM locales_source LIMIT 5;
+------+-----------------------------------------------------------+-----------+--------------+---------+
| lid  | location                                                  | textgroup | source       | version |
+------+-----------------------------------------------------------+-----------+--------------+---------+
|   61 | c:\Contributions\modules\og\og.module:597;1623            | default   | Title        | 6.25    |
|  551 | notifications_content/notifications_content.module:124;35 | default   | Content type | 6.25    |
|  981 |                                                           | default   | @count days  | 6.25    |
| 1471 | admin_menu.inc:415                                        | default   | None         | 6.25    |
| 1961 | c:\Contributions\modules\og\og.module:1059;1082           | default   | Groups       | 6.25    |
+------+-----------------------------------------------------------+-----------+--------------+---------+
5 rows in set (0.00 sec)
 
mysql> describe locales_target;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| lid         | int(11)     | NO   | PRI | 0       |       |
| translation | blob        | NO   |     | NULL    |       |
| language    | varchar(12) | NO   | PRI |         |       |
| plid        | int(11)     | NO   | MUL | 0       |       |
| plural      | int(11)     | NO   | PRI | 0       |       |
| i18n_status | int(11)     | NO   |     | 0       |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
 
mysql> mysql> SELECT * FROM locales_target LIMIT 5;
+------+-------------+----------+------+--------+-------------+
| lid  | translation | language | plid | plural | i18n_status |
+------+-------------+----------+------+--------+-------------+
|   61 | Titel       | de       |    0 |      0 |           0 |
|  551 | Inhalt      | de       |    0 |      0 |           0 |
|  981 | @count Tage | de       |    0 |      0 |           0 |
| 1471 | Kein        | de       |    0 |      0 |           0 |
| 1961 | Gruppen     | de       |    0 |      0 |           0 |
+------+-------------+----------+------+--------+-------------+
5 rows in set (0.00 sec)

[swampBoogie]

select <columnsOfInterest>
  from locales_source
  join (select lid
          from locales_target
         where language in ('de','fr','es')
         group
            by lid
        having count(*) = 3) dt
    on locales_source.lid = dt.lid

[kmillecam]

Thanks swampBoogie.

It works when I specify "source" as the <columnsOfInterest> but when I add lid to the column list, I run into a problem:

mysql> select source
    ->   from locales_source
    ->   join (select lid
    ->           from locales_target
    ->          where language in ('de','fr','es')
    ->          group
    ->             by lid
    ->         having count(*) = 3) dt
    ->     on locales_source.lid = dt.lid
    ->     limit 10;
+--------------+
| source       |
+--------------+
| Title        |
| Content type |
| @count days  |
| None         |
| Groups       |
| Type         |
| Time         |
| Available    |
| Disabled     |
| Path         |
+--------------+
10 rows in set (0.02 sec)
 
mysql> select lid, source
    ->   from locales_source
    ->   join (select lid
    ->           from locales_target
    ->          where language in ('de','fr','es')
    ->          group
    ->             by lid
    ->         having count(*) = 3) dt
    ->     on locales_source.lid = dt.lid
    ->     limit 10;
ERROR 1052 (23000): Column 'lid' in field list is ambiguous
mysql>

[r937]

Column 'lid' in field list is ambiguous means that you have to qualify it just like you did in the ON clause

[kmillecam]

Works like a charm!

mysql> select ls.lid, ls.source
    ->   from locales_source ls
    ->   join (select lid
    ->           from locales_target
    ->          where language in ('de','fr','es')
    ->          group
    ->             by lid
    ->         having count(*) = 3) dt
    ->     on ls.lid = dt.lid
    ->     limit 10;
+--------+--------------+
| lid    | source       |
+--------+--------------+
|     61 | Title        |
|    551 | Content type |
|    981 | @count days  |
|   1471 | None         |
|   1961 | Groups       |
|   2341 | Type         |
|   6531 | Time         |
|  21741 | Available    |
|  60701 | Disabled     |
| 241571 | Path         |
+--------+--------------+
10 rows in set (0.02 sec)
 
mysql>