Posted by Erland Sommarskog on 06/23/06 10:10

(chrisek@poczta.neostrada.pl) writes:
> I have 2 tables:
> - Customers with columns:
> customerID(prim_key),
> customerName(with customer's names)
> - Deliveries with columns:
> deliveryID(primKey),
> sender(ref_key to CustomerID from Customers),
> receiver(also ref_key to CustomerID from Customers);
>
> I need to select all data about deliveries, but instead of having
> sender's ID and receiver's ID, I need to have their Names.
>
> I tried to do:
>
> SELECT
> deliveries.deliveryID,
> Customers.customerName AS sender,
> Customers.customerName AS receiver
> FROM
> customers, deliveries
> WHERE
> Customers.customerID=Deliveries.sender AND
> Customers.customerID=Deliveries.receiver;
>
> But this only works if sender=receiver, which is obvious ;)
> I'd like to know if there is any other way for obtaining those data
> within one query

SELECT d.deliveryID, s.CustomerName AS sender, r.CustomerName AS receiever
FROM deliveries d
JOIN customers s ON d.sender = s.customerID
JOIN customers r ON d.receiver = r.customerID

Thus, you need to include customer twice in the query. To keep the
two instances apart, you need to use aliases. Overall, I strongly
recommend that you should always use aliases over prefixing columns
with table names. With table names repeated all over a large query,
it can be difficult to see the forest for all the trees.

As for the different join syntax, it has nothing to do with it. But once
you have started to use it, you never want to back to the older syntax
(which still is perfectly legal). In any case, you need to use the
newer syntax for outer joins.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]