[sqlite] 3-way join fails

[Jonathan Ellis]

Not sure what's wrong with this SQL; it works fine with postgresql:

sqlite> SELECT * FROM books LEFT OUTER JOIN (users JOIN loans ON users.name=
loans.user_name) ON books.id = loans.book_id;
SQL error: no such column: loans.book_id

The table definitions are as follows:

CREATE TABLE books (
   id                   integer PRIMARY KEY,
   title                text NOT NULL,
   published_year       char(4) NOT NULL,
   authors              text NOT NULL
);

CREATE TABLE users (
   name                 varchar(32) PRIMARY KEY,
   email                varchar(128) NOT NULL,
   password             varchar(128) NOT NULL,
   classname            text,
   admin                int NOT NULL -- 0 = false
);

CREATE TABLE loans (
   book_id              int PRIMARY KEY REFERENCES books(id),
   user_name            varchar(32) references users(name)
       ON DELETE SET NULL ON UPDATE CASCADE,
   loan_date            date DEFAULT current_timestamp
);