Re: Multiple lookups on query

What about goos Sent from my iPhone > On Feb 10, 2022, at 6:34 PM, paulf@quillandmouse.com wrote: > > 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