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!

You assign an alias to the client table, for example:

SELECT InvoiceTable.invoice_id, ClientTable.client_name,
ConsigneeTable.client_name FROM (InvoiceTable INNER JOIN ClientTable
ON InvoiceTable.client_id = ClientTable.client_id) INNER JOIN
ClientTable AS ConsigneeTable ON InvoiceTable.consignee_id =
ConsigneeTable.client_id;

On these types of queries, don't try to drop the table names as
qualifiers, use them to you advantage.

Mark

On 12 Feb 2004 08:53:06 -0800, luisjahn (AT) hotmail (DOT) com (Pikabu) wrote: