[SOLVED] Need SELECT statement: Table A has two fields which both link to Table B

[GreenAlien]

If I have the following two tables (simplified) where both Buyer and Recipient fields are Customer IDs.

 

Sales

- ID

- Buyer

- Recipient

- Product

 

Customers

- ID

- Name

 

How can I retrieve a recordset with fields: Buyer Name, Recipient Name, Product ?

 

I can include either Buyer name, or Recipient name, but not sure how to include both.

 

I could include the Buyer Name and Recipient ID then do another select to get the Recipient Name but there's got to be a more optimal way of doing it...

 

Anyone? Thanks.

 

PS: Having separate tables for buyer and recipient is not an option. There would be a load of redundant info if I did that (i missed out several fields).

[toplay]

Try something like:

 

SELECT

              b.Name AS Buyer_Name, r.Name AS Recipient_Name, s.Product

  FROM

              Sales s

    JOIN

              Customers b

      ON

              b.ID = s.Buyer

    JOIN

              Customers r

      ON

              r.ID = s.Recipient

;

 

[GreenAlien]

Excellent. That worked, thanks.