Multiple lookups on query

Folks:

I'm not that great with joins, and I've always sort of worked around
this problem, but maybe you have a better way. I'm using SQLite3, but
any SQL db will do

Assume a tables:

create table users {
id integer primary key autoincrement,
name varchar(50)
};

create table invoices {
vendor integer references users(id),
customer integer references user(id)
}

Now if I want to do a query over the invoices table which includes both
the vendor name and the customer name, I have two joins against the same
users table. It doesn't work. Like this:

SELECT u.name as custname, u.name as vendname FROM invoices as i, users
as u WHERE i.vendor = u.id AND i.customer = u.id;

Normally, I just fetch the customer name records (in PHP) and then
iterate over them again to fetch the vendor name. But there should be a
better way. Anyone know one?

Paul

-- 
Paul M. Foster
http://noferblatz.com
http://quillandmouse.com