Double Joins

[Pikabu]

I have an invoices database with three fields:

Invoice_id Integer
Client_id Integer
Consignee_id Integer

I also have a clients database with two fields:

Client_id Integer
Client_name char(100)

Both the client_id and the consignee_id refer to records of the
clients table (clients and consignees are stored in the clients
table), so for each invoice I have two related records in the clients
table.

The problem I have is how to retrieve the invoice_id and the name of
both the client and the consignee using a single query.

I can use the following statement to get the invoice id and the client
name, but how do I retrieve the consignee name in the same select?

SELECT invoice_id,client_name FROM invoices INNER JOIN clients ON
invoices.client_id=clients.client_id

Thanks for any help!

Luis

 

[Douglas J. Steele]

You need to include the clients table twice in the query. Alias it as
consignees once to avoid confusion:


SELECT invoices.Invoice_id,
clients.Client_name AS Client_name,
consignees.Client_name AS Consignee_name
FROM (clients
INNER JOIN invoices
ON clients.Client_id = invoices.Client_id)
INNER JOIN clients AS consignees
ON invoices.Consignee_id = consignees.Client_id

 

[news.microsoft.com]

Thanks a lot Doug!

I didn't know access supported aliases on the table names.

Luis