--- 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 > ); >
Yet another way: SELECT * FROM books LEFT OUTER JOIN (users JOIN loans ON name=user_name) ON id = book_id; Interestingly, the above query is less efficient than: SELECT * FROM books LEFT OUTER JOIN (select book_id from users JOIN loans ON name = user_name) ON id = book_id; as the former query does the equivalent of (select * from users JOIN loans ON name=user_name) for the inner subselect. sqlite> explain SELECT * FROM books LEFT OUTER JOIN (select book_id from users JOIN loans ON name = user_name) ON id = book_id; 0|OpenEphemeral|1|1| 1|Goto|0|45| 2|Integer|0|0| 3|OpenRead|2|5| 4|SetNumColumns|2|1| 5|Integer|0|0| 6|OpenRead|3|7| 7|SetNumColumns|3|2| 8|Rewind|2|20| 9|Rewind|3|19| 10|Column|2|0| 11|Column|3|1| 12|Ne|354|18|collseq(BINARY) 13|Column|3|0| 14|MakeRecord|1|0| 15|NewRowid|1|0| 16|Pull|1|0| 17|Insert|1|0| 18|Next|3|10| 19|Next|2|9| 20|Close|2|0| 21|Close|3|0| 22|Integer|0|0| 23|OpenRead|0|4| 24|SetNumColumns|0|4| 25|Rewind|0|43| 26|MemInt|0|1| 27|Rewind|1|39| 28|Rowid|0|0| 29|Column|1|0| 30|Ne|355|38|collseq(BINARY) 31|MemInt|1|1| 32|Rowid|0|0| 33|Column|0|1| 34|Column|0|2| 35|Column|0|3| 36|Column|1|0| 37|Callback|5|0| 38|Next|1|28| 39|IfMemPos|1|42| 40|NullRow|1|0| 41|Goto|0|31| 42|Next|0|26| 43|Close|0|0| 44|Halt|0|0| 45|Transaction|0|0| 46|VerifyCookie|0|6| 47|Goto|0|2| 48|Noop|0|0| sqlite> explain SELECT * FROM books LEFT OUTER JOIN (users JOIN loans ON name=user_name) ON id = book_id; 0|OpenEphemeral|1|8| 1|Goto|0|59| 2|Integer|0|0| 3|OpenRead|2|5| 4|SetNumColumns|2|5| 5|Integer|0|0| 6|OpenRead|3|7| 7|SetNumColumns|3|3| 8|Rewind|2|27| 9|Rewind|3|26| 10|Column|2|0| 11|Column|3|1| 12|Ne|354|25|collseq(BINARY) 13|Column|2|0| 14|Column|2|1| 15|Column|2|2| 16|Column|2|3| 17|Column|2|4| 18|Column|3|0| 19|Column|3|1| 20|Column|3|2| 21|MakeRecord|8|0| 22|NewRowid|1|0| 23|Pull|1|0| 24|Insert|1|0| 25|Next|3|10| 26|Next|2|9| 27|Close|2|0| 28|Close|3|0| 29|Integer|0|0| 30|OpenRead|0|4| 31|SetNumColumns|0|4| 32|Rewind|0|57| 33|MemInt|0|1| 34|Rewind|1|53| 35|Rowid|0|0| 36|Column|1|5| 37|Ne|355|52|collseq(BINARY) 38|MemInt|1|1| 39|Rowid|0|0| 40|Column|0|1| 41|Column|0|2| 42|Column|0|3| 43|Column|1|0| 44|Column|1|1| 45|Column|1|2| 46|Column|1|3| 47|Column|1|4| 48|Column|1|5| 49|Column|1|6| 50|Column|1|7| 51|Callback|12|0| 52|Next|1|35| 53|IfMemPos|1|56| 54|NullRow|1|0| 55|Goto|0|38| 56|Next|0|33| 57|Close|0|0| 58|Halt|0|0| 59|Transaction|0|0| 60|VerifyCookie|0|6| 61|Goto|0|2| 62|Noop|0|0| __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
