Query for Many to Many

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Query for Many to Many

dboland9
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 PK        book_isbn PK        a_b_id PK
    author_fname        book_title          author_id FK
    author_lname        book_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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query for Many to Many

David Raymond
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 <[hidden email]> On Behalf Of dboland9
Sent: Tuesday, August 27, 2019 9:38 AM
To: [hidden email]
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 PK        book_isbn PK        a_b_id PK
    author_fname        book_title          author_id FK
    author_lname        book_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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query for Many to Many

Dominique Devienne
In reply to this post by dboland9
On Tue, Aug 27, 2019 at 3:38 PM dboland9 <[hidden email]> 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 PK        book_isbn PK        a_b_id PK
>     author_fname        book_title          author_id FK
>     author_lname        book_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.
>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query for Many to Many

Dominique Devienne
On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query for Many to Many

David Raymond
It does support natural joins. <Insert usual "be careful with schema changes" comments here>

USING needs parenthesis around the column list: ...using (author_id)...using (book_isbn)...


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Dominique Devienne
Sent: Tuesday, August 27, 2019 10:08 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Query for Many to Many

On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query for Many to Many

John Gillespie-2
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 <[hidden email]>
wrote:

> It does support natural joins. <Insert usual "be careful with schema
> changes" comments here>
>
> USING needs parenthesis around the column list: ...using
> (author_id)...using (book_isbn)...
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On
> Behalf Of Dominique Devienne
> Sent: Tuesday, August 27, 2019 10:08 AM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Query for Many to Many
>
> On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query for Many to Many

dboland9
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 <[hidden email]> 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 [hidden email]
> wrote:
>
> > It does support natural joins. <Insert usual "be careful with schema
> > changes" comments here>
> > USING needs parenthesis around the column list: ...using
> > (author_id)...using (book_isbn)...
> > -----Original Message-----
> > From: sqlite-users [hidden email] On
> > Behalf Of Dominique Devienne
> > Sent: Tuesday, August 27, 2019 10:08 AM
> > To: SQLite mailing list [hidden email]
> > Subject: Re: [sqlite] Query for Many to Many
> > On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne [hidden email]
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query for Many to Many

Keith Medcalf

On Tuesday, 3 September, 2019 07:19, dboland9 <[hidden email]> wrote:

>Thanks for the info.  As I understand things, this is an implicit join.  

The syntax for a SELECT statement goes like this:

SELECT <the list of what data I want>
FROM <the list of tables from whence it comes>
WHERE <the condition that defines the rows I want>
GROUP BY <the list things to group the rows I want>
HAVING <the condition to select the groups I want>
ORDER BY <the list of things to sort the results by I want>

>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 "<join type> 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 <[hidden email]>
>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 [hidden email]
>> wrote:
>>
>> > It does support natural joins. <Insert usual "be careful with schema
>> > changes" comments here>
>> > USING needs parenthesis around the column list: ...using
>> > (author_id)...using (book_isbn)...
>> > -----Original Message-----
>> > From: sqlite-users [hidden email] On
>> > Behalf Of Dominique Devienne
>> > Sent: Tuesday, August 27, 2019 10:08 AM
>> > To: SQLite mailing list [hidden email]
>> > Subject: Re: [sqlite] Query for Many to Many
>> > On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne [hidden email]
>> > 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
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> > sqlite-users mailing list
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query for Many to Many

Rowan Worth-2
On Tue, 3 Sep 2019 at 22:17, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query for Many to Many

Simon Slavin-3
On 6 Sep 2019, at 7:36am, Rowan Worth <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users