SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need query help on translation table problem

    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:

    Code MySQL:
    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)

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code MySQL:
    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>

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Column 'lid' in field list is ambiguous means that you have to qualify it just like you did in the ON clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks for the nudge r937

    Works like a charm!

    Code MySQL:
    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>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •