SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Melbourne, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joining a table onto itself

    There was a thread on this topic about a month ago, but I didn't really understand how to apply the answer to my situation - hope you don't mind if I start a new thread.

    I have a table of products with descriptions in different languages, it looks like this:

    prodcode - language - description
    AAA English Widget
    AAA Korean (Widget in Korean)
    AAA German etc...

    I want to build an array in two languages, it would be like this, for example, if the chosen languages were English and Korean:

    prodcode - description in l1 - description in l2
    AAA Widget (Widget in Korean)

    There will always be an English description, but other languages may not exist - that is, there may be no entry in the table for that product and language. In this case, I want the table to show:

    prodcode - description in l1 - description in l2
    AAA Widget null

    I tried the following SELECT statement, but it didn't return any rows.

    Code MySQL:
    SELECT * FROM prodtable 
    LEFT OUTER JOIN prodtable AS langtrans ON prodtable.prodcode = langtrans.prodcode 
    WHERE prodtable.langcode = 'English' AND langtrans.langcode = '$translang'
    (where $translang has the value of the second language, in this case Korean)

    I'm new to outer joins and also have never used subqueries or CASE, so any help would be appreciated.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Putting a criteria in the WHERE on the second table, the left join becomes an inner join.
    Move the AND after the ON, and it should work
    Code:
    SELECT * 
    FROM prodtable
    LEFT OUTER JOIN prodtable AS langtrans 
    ON prodtable.prodcode = langtrans.prodcode
    AND langtrans.langcode = '$translang'
    WHERE prodtable.langcode = 'English'

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    Melbourne, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Magic! Thanks.


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
  •