[thelist] SQL: two tables, confused about joins, help!

[Chris W. Parker]

hi.

i've got two tables...

sfOrders:
----------------
orderID, orderCustID

sfCustomers:
----------------
custFirstName, custID


when i need to do is retrieve custFirstName.sfCustomers knowing only
orderID.sfOrders.

the way i'm doing that so far is like this...(btw i'm using access/asp)

(watch wrap)
SELECT orderCustID,
  (SELECT custFirstName FROM sfCustomers WHERE custID = orderCustID)
FROM sfOrders
WHERE orderID = 1

it works except i have a few questions about it.

1. what do i need to change so that i can refer to the second column by
a name instead of a number? what i am doing now is
rsCustInfo("orderCustID") for the first column and rsCustInfo(1) for the
second column. i tried calling it using "custFirstName" but that didn't
work. i also tried do (SELECT...) AS firstName and calling it with
firstName. that didn't work either.

2. is this the best way to do this? what kind of a JOIN in this?



thanks,
chris.