Re: [sqlite] Query for Many to Many
On 6 Sep 2019, at 7:36am, Rowan Worth wrote: > I was surprised when this behaved differently in other SQL engines. eg. in > SQLite you can write: > > SELECT col1, col2 FROM table1, table2 USING But please don't, for the reason you gave. Not only is it ambiguous but different SQL engines interpret it differently. SQLite uses PostgreSQL as a model for many things but here they diverge. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
On Tue, 3 Sep 2019 at 22:17, Keith Medcalf wrote: > And the "," in the list of tables may be replaced by the word JOIN. It is > merely an alternate spelling. > I was surprised when this behaved differently in other SQL engines. eg. in SQLite you can write: SELECT col1, col2 FROM table1, table2 USING (commonId) But in eg. postgres it must be written using "table1 JOIN table2" rather than the comma, because postgres treats "table1, table2" as "table1 JOIN tabel2 ON TRUE" resulting in a conflict with the USING clause. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
On Tuesday, 3 September, 2019 07:19, dboland9 wrote: >Thanks for the info. As I understand things, this is an implicit join. The syntax for a SELECT statement goes like this: SELECT FROM WHERE GROUP BY HAVING ORDER BY >I did try it, and it works just fine. However, in my reading it appears that >implicit joins are discouraged, and will only do left joins. Implicit joins (whatever that means) are not discouraged. And the "," in the list of tables may be replaced by the word JOIN. It is merely an alternate spelling. And the word ON is merely an alternate spelling of AND (plus some parenthesis -- you put parenthesis around the existing WHERE clause, put parenthesis around the ON clause, change the word ON to AND, and tack the result to the end of the (now parenthesized) WHERE clause). Using "commas" instead of "JOIN" there is no way to specify a particular join type, so "," always means "INNER JOIN" -- so if you need to specify a JOIN type other than an plain projection (inner) you must use the table " table" specification. Except for OUTER JOIN operations, the ON clause is merely a WHERE condition (in outer joins the ON clause binds the table that is being outer joined). In the olden days one used the special operator *= or =* or *=* to indicate outer joins in the where clause). The ON clause does not even need to contain references to tables that have already been mentioned since they are merely syntactic sugar. You can even have one without using the word JOIN at all as in "SELECT a,b,c FROM x,y,z ON x.a = y.b and y.g = z.c WHERE z.c = 5 or x.a = 3" which translates to "SELECT a,b,c FROM x,y,z WHERE (z.c = 5 or x.a = 3) and x.a = y.b and y.g = z.c" You will notice that all the things in the SELECT statement are defined as "I want". This is what makes SQL a declarative language -- you declare what you want, and it figures out how to go get it. >Also, it looks like you are using aliasing to shorten the query strings - >true or false? Yes. The tables are aliased and the as keyword is omitted. >‐‐‐ Original Message ‐‐‐ >On Tuesday, September 3, 2019 7:32 AM, John G >wrote: > >> Or without the added calories (syntactic sugar) : >> >> select a., b. >> from author_books ab, author a, books b >> where a.author_id = ab.author_id >> and b.book_isbn = ab.book_isbn >> >> On Tue, 27 Aug 2019 at 15:52, David Raymond david.raym...@tomtom.com >> wrote: >> >> > It does support natural joins. > > changes" comments here> >> > USING needs parenthesis around the column list: ...using >> > (author_id)...using (book_isbn)... >> > -Original Message- >> > From: sqlite-users sqlite-users-boun...@mailinglists.sqlite.org On >> > Behalf Of Dominique Devienne >> > Sent: Tuesday, August 27, 2019 10:08 AM >> > To: SQLite mailing list sqlite-users@mailinglists.sqlite.org >> > Subject: Re: [sqlite] Query for Many to Many >> > On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne ddevie...@gmail.com >> > wrote: >> > >> > > select author., books. >> > > from author_books >> > > join author on author.author_id = author_books.author_id >> > > join books on books.book_isbn = author_books.book_isbn >> > >> > Which can also be written: >> > select author., books. >> > from author_books >> > join author using author_id >> > join books using book_isbn >> > Or even: >> > select author., books. >> > from author_books >> > natural join author >> > natural join books >> > All of the above untested of course :). >> > Not even sure SQLite supports natural join or not (I'd guess it does). >--DD >> > https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on >> > https://stackoverflow.com/questions/8696383/difference-between-natural- >join-and-inner-join >> > >> > sqlite-users mailing list >> > sqlite-users@mailinglists.sqlite.org >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >> > sqlite-users mailing list >> > sqlite-users@mailinglists.sqlite.org >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
Thanks for the info. As I understand things, this is an implicit join. I did try it, and it works just fine. However, in my reading it appears that implicit joins are discouraged, and will only do left joins. Also, it looks like you are using aliasing to shorten the query strings - true or false? Dave, Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Tuesday, September 3, 2019 7:32 AM, John G wrote: > Or without the added calories (syntactic sugar) : > > select a., b. > from author_books ab, author a, books b > where a.author_id = ab.author_id > and b.book_isbn = ab.book_isbn > > On Tue, 27 Aug 2019 at 15:52, David Raymond david.raym...@tomtom.com > wrote: > > > It does support natural joins. > changes" comments here> > > USING needs parenthesis around the column list: ...using > > (author_id)...using (book_isbn)... > > -Original Message- > > From: sqlite-users sqlite-users-boun...@mailinglists.sqlite.org On > > Behalf Of Dominique Devienne > > Sent: Tuesday, August 27, 2019 10:08 AM > > To: SQLite mailing list sqlite-users@mailinglists.sqlite.org > > Subject: Re: [sqlite] Query for Many to Many > > On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne ddevie...@gmail.com > > wrote: > > > > > select author., books. > > > from author_books > > > join author on author.author_id = author_books.author_id > > > join books on books.book_isbn = author_books.book_isbn > > > > Which can also be written: > > select author., books. > > from author_books > > join author using author_id > > join books using book_isbn > > Or even: > > select author., books. > > from author_books > > natural join author > > natural join books > > All of the above untested of course :). > > Not even sure SQLite supports natural join or not (I'd guess it does). --DD > > https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on > > https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join > > > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
Or without the added calories (syntactic sugar) : select a.*, b.* from author_books ab, author a, books b where a.author_id = ab.author_id and b.book_isbn = ab.book_isbn On Tue, 27 Aug 2019 at 15:52, David Raymond wrote: > It does support natural joins. changes" comments here> > > USING needs parenthesis around the column list: ...using > (author_id)...using (book_isbn)... > > > -Original Message- > From: sqlite-users On > Behalf Of Dominique Devienne > Sent: Tuesday, August 27, 2019 10:08 AM > To: SQLite mailing list > Subject: Re: [sqlite] Query for Many to Many > > On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne > wrote: > > > select author.*, books.* > > from author_books > > join author on author.author_id = author_books.author_id > > join books on books.book_isbn = author_books.book_isbn > > > > Which can also be written: > > select author.*, books.* > from author_books > join author using author_id > join books using book_isbn > > Or even: > > select author.*, books.* > from author_books > natural join author > natural join books > > All of the above untested of course :). > Not even sure SQLite supports natural join or not (I'd guess it does). --DD > > https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on > > https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
It does support natural joins. USING needs parenthesis around the column list: ...using (author_id)...using (book_isbn)... -Original Message- From: sqlite-users On Behalf Of Dominique Devienne Sent: Tuesday, August 27, 2019 10:08 AM To: SQLite mailing list Subject: Re: [sqlite] Query for Many to Many On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne wrote: > select author.*, books.* > from author_books > join author on author.author_id = author_books.author_id > join books on books.book_isbn = author_books.book_isbn > Which can also be written: select author.*, books.* from author_books join author using author_id join books using book_isbn Or even: select author.*, books.* from author_books natural join author natural join books All of the above untested of course :). Not even sure SQLite supports natural join or not (I'd guess it does). --DD https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne wrote: > select author.*, books.* > from author_books > join author on author.author_id = author_books.author_id > join books on books.book_isbn = author_books.book_isbn > Which can also be written: select author.*, books.* from author_books join author using author_id join books using book_isbn Or even: select author.*, books.* from author_books natural join author natural join books All of the above untested of course :). Not even sure SQLite supports natural join or not (I'd guess it does). --DD https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
On Tue, Aug 27, 2019 at 3:38 PM dboland9 wrote: > I need some help writing some queries for a MTM relationship. The example > tables are: > > author table books table author_books table > author_id PKbook_isbn PKa_b_id PK > author_fnamebook_title author_id FK > author_lnamebook_pub_date book_isbn FK > author_minit > > > Listings desired: > book_isbn book_title book_pub_date author > --++--+--- > > author book_isbnBook_title > +-+ > > Would appreciate the query (inner join - that I do know), and why so I can > learn something from them. Please keep them simple (no alias or other > shortcuts) so I can easily follow what you are doing. Thanks in advance. > Well, that's all you need, inner join, just two of them. Nothing difficult here IMHO. Or I'm missing something. --DD > I assume the query will be something like: > SELECT > books.book_isbn, books.book_title, books.book_pub_date, > author.author_fname, author.author_minit, > author.author_lname > FROM books > JOIN > author_books ON (something ) select author.*, books.* from author_books join author on author.author_id = author_books.author_id join books on books.book_isbn = author_books.book_isbn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
The basic query is going to be the below select stuff from books inner join author_books on author_books.book_isbn = books.book_isbn inner join author on author_books.author_id = author.author_id where things; -Original Message- From: sqlite-users On Behalf Of dboland9 Sent: Tuesday, August 27, 2019 9:38 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Query for Many to Many All, I need some help writing some queries for a MTM relationship. The example tables are: author table books table author_books table author_id PKbook_isbn PKa_b_id PK author_fnamebook_title author_id FK author_lnamebook_pub_date book_isbn FK author_minit Listings desired: book_isbn book_title book_pub_date author --++--+--- author book_isbn Book_title +-+ Would appreciate the query (inner join - that I do know), and why so I can learn something from them. Please keep them simple (no alias or other shortcuts) so I can easily follow what you are doing. Thanks in advance. I assume the query will be something like: SELECT books.book_isbn, books.book_title, books.book_pub_date, author.author_fname, author.author_minit, author.author_lname FROM books JOIN author_books ON (something ) Dave, Sent with ProtonMail Secure Email. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query for Many to Many
All, I need some help writing some queries for a MTM relationship. The example tables are: author table books table author_books table author_id PKbook_isbn PKa_b_id PK author_fnamebook_title author_id FK author_lnamebook_pub_date book_isbn FK author_minit Listings desired: book_isbn book_title book_pub_date author --++--+--- author book_isbn Book_title +-+ Would appreciate the query (inner join - that I do know), and why so I can learn something from them. Please keep them simple (no alias or other shortcuts) so I can easily follow what you are doing. Thanks in advance. I assume the query will be something like: SELECT books.book_isbn, books.book_title, books.book_pub_date, author.author_fname, author.author_minit, author.author_lname FROM books JOIN author_books ON (something ) Dave, Sent with ProtonMail Secure Email. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
