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

[matt g]

> From: "Chris W. Parker" <cparker at swatgear.com>

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

Here's what you do to get the customer's name associated with any particular
order:
SELECT sfCustomers.custFirstName, sfOrders.orderID
FROM sfCustomers, sfOrders
WHERE sfCustomers.custID = sfOrders.orderCustID
    AND sfOrders.orderID = 1 (or whatever order number you want) ;


> 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.
I'm not sure what you're asking, but you can call all fields by their name
this way: tableName.fieldName so, for your example sfOrders.orderID,
sfOrders, orderCustID, sfCustomers.custFirstName, sfCustomers.custID

> 2. is this the best way to do this? what kind of a JOIN in this?
That's not a join, it's a query with a sub query. The example I used above
is a flavor of Inner Join.

Hope that helps.
matt g