SQLite is having trouble resolving loans.book_id without an alias... SELECT * FROM books LEFT OUTER JOIN ( select loans.book_id LBID from users JOIN loans ON users.name = loans.user_name ) ON books.id = LBID;
--- Jonathan Ellis <[EMAIL PROTECTED]> wrote: > 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 > ); > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
