SQL query question with multi-tables in many-to-many

From: Dave <dboland9_at_protonmail.com>
Date: Thu, 26 Sep 2019 11:59:18 -0400
Message-ID: <qmin8l$1k9m$1_at_gioia.aioe.org>

[Quoted] I have two tables and a join, or bridge table. I'm able to get the query I need to work using the older style implicit join (WHERE clauses), and understand why it works. I have also be able to get it to work (with some help) using JOIN/ON. The problem is that I really don't understand why it must be written the way it is. I would appreciate either a pointer to a tutorial on the subject, or perhaps someone can explain it in detail.

Lets use the classic authors and books tables and a join table.

Authors:		Books:			AuthorsBooks:
AuthID*			ISBN*			AuthID**
AuthName		Title			ISBN**
			Descripn

*primary key **foreign key

The correct general query method seems to be:

| SELECT all-column-names-needed--fully-qualified
| FROM bridge-table
| JOIN table1 ON table1.primarykey = bridge-table.foreignkey
| JOIN table2 ON table2.primarykey = bridge-table.foreignkey

The actual query may be:

| SELECT Authors.AuthID, Authors.AuthName, Books.ISBN, Books.Title,
Books.Descripn

| FROM AuthorsBooks
| JOIN Authors ON Authors.AuthID = AuthorsBooks.AuthID
| JOIN Books ON Books.ISBN = AuthorsBooks.ISBN

Thanks,
Dave Received on Thu Sep 26 2019 - 17:59:18 CEST