SQLTeam.com | Weblogs | Forums

Syntax needed for simple search querying two fields from same table

sql2008r2

#1

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,
          d.buyerstoreid,
          s.name,
          d.sellershippedamt 
from deals as d join 
       stores as s on d.sellerstoreid = s.storeid 
where d.datecreated > '2015-07-01'`

#2

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.


#3

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