Alan-
I'm confused about what the relations object and aliases have to do with this exactly...But here's my best take on what you are asking (using the PUsite for example).

From the help:

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]]
  [Alias.] Select_Item [AS Column_Name]
  [, [Alias.] Select_Item [AS Column_Name] ...]

FROM [FORCE]
[DatabaseName!]Table [[AS] Local_Alias]
  [[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN
     DatabaseName!]Table [[AS] Local_Alias]
     [ON JoinCondition …]


>FROM Orders INNER JOIN Sites PUSite ON PUSite.Site = PICKUP_SITE;

For example: 
SELECT Orders.*, PUsite.Site FROM Orders INNER JOIN Sites PUsite ON PUsite.Site = Orders.PUsite

>How can I specify an alias for a table name in the join condition? For example, I have a table called ORDERS that holds customer order information.  There is a field in ORDERS called PICKUP_SITE and another called DELIVERY_SITE. These are foreign keys into my SITES table.  How can I set up my cursor and/or relation objects so that the resulting join condition looks like this:
>            INNER JOIN Sites DLSite ON DLSite.Site = DELIVERY_SITE
>
>I tried several different things including setting the Alias2 of the relation object to include the alias name (like "Sites PUSite") but without much success.