By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

2 foreign keys to the same table - multitable selection query problem

P: n/a
Hello,

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

Thank you very much for your help
Chris

Jun 23 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
(ch*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 23 '06 #2

P: n/a
WOW!
That was quick!
Thanx dude - I'll try this one!

Jun 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.