SQL to join two tables

Programming in sql: SQL to join two tables on newest questions tagged sql – Stack Overflow

I have a question on how to join two tables, I have the following tables

ITEM_TAB
--------------------------------------------------
ItemID, Qty, Price, EleCode, WomCode, MenCode
--------------------------------------------------

CODES_TAB
--------------------------------------------------
CODE     |   TYPE             |        DESCRIPTION
--------------------------------------------------
AA       |     ELECTRONICS    |          ...
AA       |    WOMEN           |           ...
AA       |   MENS             |          ...
BB       |    GROCERY         |           ....
BB       |     DELI           |
--------------------------------------------------

Item table contains only codes(EleCode is of TYPE Electronics, WomCode is of TYPE Womens etc), and CODES is a lookup table. These two do not have any association. In a single query I need to retrieve like the following.

ItemID, Qty, Price, EleCode, Desction, WomCode, Description, MenCode, Description.

select i.itemId
    , i.Qty
    , i.price
    , i.EleCode
    , (select description from code where code='AA' and TYPE='ELECTRONICS')
    , i.WomCode
    , (select description from code where code='AA' and TYPE='WOMEN')
    , i.MenCode
    , (select description from code where code='AA' and TYPE='MENS')
from ITEM

Could you please help me in framing this query.

source: http://stackoverflow.com/questions/11424304/sql-to-join-two-tables
Programming in sql: programming-in-sql