many-to-many query -- buyers and sellers

[Ross]

Hi,

I'm trying to do something that seems very simple, but has me stumped.
The real application I'm working on is quite complex, so I'll just use
a "buyers and sellers" analogy, which sort of summarizes what I'm
really trying to do.

Basically, I have 2 tables:
Table 1: Users
field 1: user_id
field 2: username

Table 2: Transactions
field 1: transaction_id
field 2: buyer_id
field 3: seller_id

So, what I'm trying to do is build a single query that will return the
usernames of both the buyer and the seller.

Your help is greatly appreciated.

Ross

[Bob Barrows]

Ross wrote:

> Hi,
>
> I'm trying to do something that seems very simple, but has me stumped.
> The real application I'm working on is quite complex, so I'll just use
> a "buyers and sellers" analogy, which sort of summarizes what I'm
> really trying to do.
>
> Basically, I have 2 tables:
> Table 1: Users
> field 1: user_id
> field 2: username
>
> Table 2: Transactions
> field 1: transaction_id
> field 2: buyer_id
> field 3: seller_id
>
> So, what I'm trying to do is build a single query that will return the
> usernames of both the buyer and the seller.
>
> Your help is greatly appreciated.
>
> Ross

What database? I'm going to assume SQL Server. I will not be happy if you
are using Access because that will change the syntax required in the query.
The good news (for you) is that you will be able to use the Access Query
Builder to do what I describe below.

I assume buyer_id and seller_id both point to a record in the Users table.
You need to use a table alias to join to the Users tables twice:

Select b.surname as Buyer, s.surname as Seller
FROM Transactions t
INNER JOIN Users As b ON t.buyer_id = b.user_id
INNER JOIN Users As s ON t.seller_id = s.user_id

If using Access, use the Query Builder Design View and add in the Users
table twice, creating the joins to the appropriate field in Transactions
from each copy of the Users table.

HTH,
Bob Barrows



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Chris Hohmann]

"Ross" <rolo@houston.rr.com> wrote in message
news:99b34533.0311101103.1308495a@posting.google.c om...

> Hi,
>
> I'm trying to do something that seems very simple, but has me stumped.
> The real application I'm working on is quite complex, so I'll just use
> a "buyers and sellers" analogy, which sort of summarizes what I'm
> really trying to do.
>
> Basically, I have 2 tables:
> Table 1: Users
> field 1: user_id
> field 2: username
>
> Table 2: Transactions
> field 1: transaction_id
> field 2: buyer_id
> field 3: seller_id
>
> So, what I'm trying to do is build a single query that will return the
> usernames of both the buyer and the seller.
>
> Your help is greatly appreciated.
>
> Ross

SELECT
T.transaction_id,
B.username AS buyer,
S.username AS seller
FROM
Transactions AS T,
Users AS B,
Users AS S
WHERE
T.buyer_id = B.user_id AND
T.seller_id = S.user_id

Notes:
1. Your table names are dangerously close to the reserved words
TRANSACTION and USER. Please see [url]www.aspfaq.com/2080[/url] for a list of
reserved words.

2. In the future please specify the what database and version you are
using.


HTH
-Chris Hohmann

[Ross]

Thank you very much for the help. And I appologize for the missing
details of my question. I am using SQL Server. Also, my table names in
the original post are not actually the ones in my real application. I
used those names only for illustration purposes.

Thanks again for the help! I now understand where I was going wrong
with my inner joins.

Ross