On Thu, 10 Feb 2022 17:46:08 -0500
<paulf@xxxxxxxxxxxxxxxxx> 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
