SQL: 2 references from one table (1 Viewer)

[morespamforya]

Hi

Just a little help on a seemingly easy question.

Say I have 2 tables.

1. Transactions
2. Clients

Each transaction has two clients. So transaction has client1ID, and client2ID

With one client, I can just make an SQL query to do this:

select t.*, c.name from transactions t, clients c
where c.client1ID = c.ID;

Which will show a client's name stacked against a transaction.

However, with 2 references to the same table, is there anyway to show the name of BOTH references with one query?

 

[jzwp22]

Technically speaking, if a transaction has more than 1 associated client, then you have a one-to-many relationship which warrants another table


tblTransactionClients
-pkTransClientsID primary key, autonumber
-fkTransID foreign key to your transaction table
-fkClientID foreign key to your client table

In either case, you would need a nested/sub query to show the second client on the same query result row as the first.

 

[morespamforya]

Oh ok thanks very much

I was hoping there's a simpler answer

 

[jzwp22]

After some more thought, I think there may be an easier way. In design grid view of the query, add both the client table and the transaction table. Remove one of the relationships. Now add the client table again (Access will call it tblClient_1) and establish a relationship between tblClient_1 and the second client field of the transaction table. The query would look something like this:


SELECT tblTransactions.pkTransID, tblTransactions.dteTrans, tblClients.txtFName, tblClients_1.txtFName
FROM tblClients AS tblClients_1 INNER JOIN (tblClients INNER JOIN tblTransactions ON tblClients.pkClientID = tblTransactions.fkClient1ID) ON tblClients_1.pkClientID = tblTransactions.fkClient2ID;

I've attached a sample database that illustrates this.