SQLTeam.com | Weblogs | Forums

Syntax needed for simple search querying two fields from same table



We have a table that contains all the purchase information for a particular transaction [deals] which lists both the buying party and the selling party listed by their unique ID's found within the [stores] table. I am looking for a easy way to perform a query that would list the buyer and seller names instead of their unique ID. I am not nearly as proficient as many of you likely are so have been slogging through a select query joining the stores table to get either the buyer or the seller then having to use a separate query to identify the other party but I am sure a better way exists. So for an example the current code I would use is as follows.

select d.datecreated,
from deals as d join 
       stores as s on d.sellerstoreid = s.storeid 
where d.datecreated > '2015-07-01'`


are the buyer and seller id's on the same row in the stores table. If not use two joins.
JOIN stores AS s1 ON D.sellerstoredid = s1.storeid JOIN stores AS s2 ON d.buyerstoreid = s2.storeid.


I knew it had to be something simple, thank you.