this also works:
SELECT * FROM books LEFT OUTER JOIN (
select book_id from users JOIN loans ON name = user_name
) ON id = book_id;
--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> 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
