Multiple instances of the same table in a query

[chestertb]

Hi,

I have a link table that joins individual records together from the same table.

It contains a reference to a customer, and to a distributor. From our perspective, our customers and our distributors are one in the same, that is, all distributors are also customers.

The link table looks something like this

rn crn drn
1 2 1
2 5 1
3 10 6
4 7 0

the people table looks something like this
rn name company
1 John the big spa
2 Jill
5 Pete
6 Jane janes spa & grill
7 Greg
10 Angela

Customers 2 & 5 are clearly linked to distributor 1, and customer 10 is linked to distributor 6, while customer 7 isn't linked to a distributor at all.

I want to create a joined table so that I get this...

rn crn drn name company
1 2 1 Jill the big spa
2 5 1 Pete the big spa
3 10 6 Angela janes spa & grill
4 7 0 Greg

How do I construct a query to reference multiple instances of the same table?

I've tried this
"SELECT * FROM link, people, people WHERE people.rn = link.crn AND people.rn = link.drn"

Of course, it didn't work.

Thanks
CTB

[GaryS]

Need to use AS, something like this:

"SELECT * FROM link, people AS people1, people As people2 WHERE people1.rn = link.crn AND people2.rn = link.drn"

(Just an example of how AS might be used: haven't looked through your requirements in detail - the query may be entirely wrong for what you're trying to do.)

[chestertb]

Thanks Gary,
I'll give that a shot.
Cheers
CTB

[russell]

what database are u using?

This would work in SQL Server

SELECT l.rn, l.crn, p.name, d, company
FROM linkTable l
JOIN people p
on p.rn = l.crn
JOIN people d
On d.rn = l.rn