CODEWITHSUNDEEP
sqlms-access
BeardedSith
BeardedSith

Reputation: 129

Two IDs from same table in query - trying to get names for both IDs

I have a couple of tables I'm trying to link together. One table (tblBrokeragePurchase) has the information regarding a purchase order completed by a customer. Within that table exists a BuyerID and a SellerID. Both of those fields link to another table (tblContacts). I'm wanting to output the full names of both the Buyer and the Seller, but Access is giving me some grief regarding the fields are being used in multiple aspects of my SQL statement.

So far, this is what I've got going on for myself:

SELECT [First Name] & " " & [Last Name] AS BuyerName, 
       [First Name] & " " & [Last Name] AS SellerName 
FROM   (tblBrokeragePurchase 
        LEFT JOIN tblContacts 
               ON ( tblBrokeragePurchase.SellerID = tblContacts.ID ) 
                  AND ( tblBrokeragePurchase.BuyerID = tblContacts.ID )) 
       INNER JOIN qryCityLookup 
               ON tblBrokeragePurchase.ID = qryCityLookup.ID 
WHERE  ( ( [BuyerID] = [ID] ) 
         AND ( [SellerID] = [ID] ) );

I'm decently sure it has to do with the WHERE statement. The Buyer and the Seller will not be the same person (generally?). So where do I need to fix up the JOIN or WHERE statement in my SQL?

Upvotes: 1

Views: 1324

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

Since your query will be accessing two separate records from the tblContacts table for each record in your tblBrokeragePurchase, the tblContacts record will need to be referenced twice so that two separate records can be obtained by the query simultaneously.

In MS Access, the following should be valid SQL:

select
    b.[First Name] & " " & b.[Last Name] as BuyerName, 
    s.[First Name] & " " & s.[Last Name] as SellerName 
from
    (
        (
            tblBrokeragePurchase p left join tblContacts s on p.SellerID = s.ID
        ) 
        left join tblContacts b on p.BuyerID = b.ID
    )
    inner join qryCityLookup c on p.ID = c.ID 
where
    p.BuyerID  = [YourBuyerID] and 
    p.SellerID = [YourSellerID]

The additional parentheses are used as MS Access requires that each pair of joined tables/queries be surrounded by a separate set of parentheses - I describe the general structure here.

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You have to join to the contact table twice, and use alias to difference between boths

SELECT Buyer.[First Name] & " " & Buyer.[Last Name] AS BuyerName, 
       Seller.[First Name] & " " & Seller.[Last Name] AS SellerName 
FROM   (tblBrokeragePurchase 
        LEFT JOIN tblContacts as Seller
               ON ( tblBrokeragePurchase.SellerID = tblContacts.ID ) 
        LEFT JOIN tblContacts as Buyer
               ON ( tblBrokeragePurchase.BuyerID = tblContacts.ID ) 
       INNER JOIN qryCityLookup 
               ON tblBrokeragePurchase.ID = qryCityLookup.ID

Now if you are looking for a single ID then the WHERE clausule probably should be 

WHERE  ( ( [BuyerID] = [ID] ) 
         OR ( [SellerID] = [ID] ) );

Upvotes: 2

Related Questions