Need help with simple query

sg48 · April 9th, 2010, 11:03 AM

I have a PersonTable with PersonID, FirstName, Last Name, etc

I have an OfferTable with BuyerID, AgentId, SellerID.

I want to write a query to get the names of the Buyer, Agent, Seller from the PersonTable based on the corresponding IDs in the OfferTable.

I have tried a bunch of different things but nothing worked. Any help will be appreciated.

dparsons · April 9th, 2010, 11:30 AM

I am not a SQL Guru so there might be something more effecient but it seems something like this would work:

SELECT
p1.LastName + ', ' + p1.FirstName as Buyer,
p2.LastName + ', ' + p2.FirstName as Agent,
p3.LastName + ', ' + p3.FirstName as Seller
FROM offer o
LEFT JOIN Person p1 on p1.PersonID = o.BuyerID
LEFT JOIN Person p2 on p2.PersonID = o.AgentID
LEFT JOIN Person p3 on p3.PersonID = o.SellerID

hth
-Doug

sg48 · April 9th, 2010, 12:15 PM

Thank you for fast response. Very helpful

dparsons · April 9th, 2010, 12:18 PM

You are quite welcome. Gald it worked out for you!

-Doug