Joining a table onto itself

[pat12b]

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.

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.

[guido2004]

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

SELECT * 
FROM prodtable
LEFT OUTER JOIN prodtable AS langtrans 
ON prodtable.prodcode = langtrans.prodcode
AND langtrans.langcode = '$translang'
WHERE prodtable.langcode = 'English'

[pat12b]

Magic! Thanks.