Re: Multiple lookups on query

On Thu, 10 Feb 2022 17:46:08 -0500 <paulf@quillandmouse.com> wrote: > 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 > My apologies to the list. I failed in my due diligence before asking the question. A little more search engine research, and I found the answer. It involves multiple joins to the same table using multiple aliases for the joined table, and different join fields. Again, sorry. Paul -- Paul M. Foster http://noferblatz.com http://quillandmouse.com