How does this Access join query work in Teradata?

Question

I'm trying to determine the equivalent version of this query in Teradata. The main problem I'm experiencing is trying to figure out a way to join the same column twice using two different keys ( package_unit_id and retail_unit_id). In Access, I was able to assign an alias that allowed me to select the tbleDIRGA_ItemUnit.Unit_Name column twice. How does that work in Teradata? Let me know if I'm not being clear.

In Microsoft Access:

SELECT 

    tblDIRGA_Item.SubTeam_No, 
    tblDIRGA_ItemIdentifier.Identifier, 
    tblDIRGA_ItemUnit.Unit_Name AS PackUOM,
    tblDIRGA_ItemUnit_1.Unit_Name AS RetailUOM

FROM (((tblDIRGA_ItemIdentifier AS tblDIRGA_ItemIdentifier_1 

INNER JOIN tblDIRGA_Item 
ON tblDIRGA_ItemIdentifier.Item_Key = tblDIRGA_Item.Item_Key)

INNER JOIN tblDIRGA_ItemUnit 
ON tblDIRGA_Item.Package_Unit_ID = tblDIRGA_ItemUnit.Unit_ID) 

INNER JOIN tblDIRGA_ItemUnit AS tblDIRGA_ItemUnit_1 
ON tblDIRGA_Item.Retail_Unit_ID = tblDIRGA_ItemUnit_1.Unit_ID)

In Teradata:

In Teradata I am trying to select unit_name from the ir_item_unit_v table twice using two different keys (package_unit_id) and (retail_unit_id). How does this work like in Access above?

SELECT

    st.subteam_no
    ik.identifier
    **u.unit_name AS RetailUOM,**
    **uu.unit_name AS PackUOM**

FROM DRG_R.IR_PRICE_V ip

    JOIN ir_item_identifier_v ik
    ON ip.region = ik.region 
    AND ik.item_key = ip.item_key 

    JOIN ir_item_v i 
    ON ip.region = i.region
    AND i.item_key = ip.item_key 

    **JOIN ir_item_unit_v u AS ir_item_unit_1_v uu
    ON i.package_unit_id=u.unit_id
    AND i.region=u.region**

    **JOIN ir_item_unit_v u
    ON i.retail_unit_id=u.unit_id
    AND i.region=u.region**

Answer 1

You only need to give the table two different aliases. Maybe u1 and u2? Just make sure you properly reference the aliases in your select and join.

Answer 2

It works exactly the same in Teradata, assign different aliases:

SELECT
   st.subteam_no
   ik.identifier,
   u.unit_name AS RetailUOM,
   uu.unit_name AS PackUOM
FROM DRG_R.IR_PRICE_V ip

JOIN ir_item_identifier_v AS ik
ON ip.region = ik.region 
AND ik.item_key = ip.item_key 

JOIN ir_item_v AS i 
ON ip.region = i.region
AND i.item_key = ip.item_key 

JOIN ir_item_unit_v AS u
ON i.package_unit_id=u.unit_id
AND i.region=u.region

JOIN ir_item_unit_v AS uu
ON i.retail_unit_id=uu.unit_id
AND i.region=uu.region