SQL FROM syntax - Query

  • Thread starter Thread starter George Seese
  • Start date Start date
G

George Seese

I have a 4-table database provided from a "Learn Word VBA" book.
Trying to learn the patterns found in the SQL statement, especially in the
FROM / INNER JOIN ON.

I first made a query with only one table.
Then added another related table. The FROM was understandable.
When I select all four tables, I don't understand the syntax or format of
the SQL statement.
For example it shows (Customer.ID = Product.ID). These tables are not
related.

The VBA code works; I'm just curious about the format.
Where can I learn nore about this?

Also, what happens if you select the tables in a different sequence than the
normal?
That doesn't change any relationships does it? It does make a confusing
diagram.

Thanks,
George
 
John,
Here it is:
SELECT Customer.*
FROM Product INNER JOIN ((Customer INNER JOIN Invoice ON Customer.ID =
Invoice.Customer) INNER JOIN InvoiceItem ON Invoice.ID =
InvoiceItem.[Invoice ID]) ON (Customer.ID = Product.ID) AND (Product.ID =
InvoiceItem.[Product ID]);


George
 
I normally paraphrase it this way:

SELECT Customer.*
FROM Product
INNER JOIN
( ( Customer
INNER JOIN
Invoice
ON Customer.ID = Invoice.Customer)
INNER JOIN InvoiceItem
ON Invoice.ID = InvoiceItem.[Invoice ID])
ON (Customer.ID = Product.ID) AND
(Product.ID = InvoiceItem.[Product ID]);

(not sure how it comes out if you use proportional font)

Looking at it from the right-most identation, you can see
that Customer is joined to Invoice (1st join). The result
of the 1st join is then joined to InvoiceItem (2nd join).
The result of the second join is then joined to Product.

See the JET SQL Reference in Access Help for lots of info.
on JET SQL (Note: JET is the default database engine used
in Access).

HTH
Van T. Dinh
MVP (Access)
 
Thanks for your replies.

Please consider this progression from simple to complicated:

(1) Selection of Customer and Invoice tables, the SQL is:
FROM Customer INNER JOIN Invoice ON Customer.ID = Invoice.Customer;

(2) Selection of Invoice and InvoiceItem tables:
FROM Invoice INNER JOIN InvoiceItem ON Invoice.ID = InvoiceItem.[Invoice
ID];

(3) Selection of Product and InvoiceItem tables:
FROM Product INNER JOIN InvoiceItem ON Product.ID = InvoiceItem.[Product
ID];

(4) Selection of Customer, Invoice, and InvoiceItem tables:
FROM (Customer INNER JOIN Invoice ON Customer.ID = Invoice.Customer)
INNER JOIN InvoiceItem ON Invoice.ID = InvoiceItem.[Invoice ID];
---
This is easily seen as a combination of (1) and (2).

(5) Selection of Customer, Invoice, InvoiceItem, and Product tables:
FROM Product INNER JOIN ((Customer INNER JOIN Invoice ON Customer.ID =
Invoice.Customer)
INNER JOIN InvoiceItem ON Invoice.ID = InvoiceItem.[Invoice ID])
ON (Customer.ID = Product.ID) AND (Product.ID = InvoiceItem.[Product ID]);
---
Questions:
-How is this SQL statement generated - what are the successive logical
steps?
-Why does it start with Product instead of Customer?
-In (1) to (4), each INNER JOIN has an ON. In (5) an INNER JOIN is followed
by two ONs
and an AND.
-Why is (Customer.ID = Product.ID) included? This is not one of the three
defined relationships with these
four tables. There's probably a good reason it's included.

John - I'm not attempting to change the relationships in the query; there
should be no confusion.
Why should there be any "guessing by the wizard"?

George
 
Van,
The JET SQL Reference in Access Help (Data Manipulation Language section)
does help.
Thanks,
George


Van T. Dinh said:
I normally paraphrase it this way:

SELECT Customer.*
FROM Product
INNER JOIN
( ( Customer
INNER JOIN
Invoice
ON Customer.ID = Invoice.Customer)
INNER JOIN InvoiceItem
ON Invoice.ID = InvoiceItem.[Invoice ID])
ON (Customer.ID = Product.ID) AND
(Product.ID = InvoiceItem.[Product ID]);

(not sure how it comes out if you use proportional font)

Looking at it from the right-most identation, you can see
that Customer is joined to Invoice (1st join). The result
of the 1st join is then joined to InvoiceItem (2nd join).
The result of the second join is then joined to Product.

See the JET SQL Reference in Access Help for lots of info.
on JET SQL (Note: JET is the default database engine used
in Access).

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
John,
Here it is:
SELECT Customer.*
FROM Product INNER JOIN ((Customer INNER JOIN Invoice ON Customer.ID =
Invoice.Customer) INNER JOIN InvoiceItem ON Invoice.ID =
InvoiceItem.[Invoice ID]) ON (Customer.ID = Product.ID) AND (Product.ID =
InvoiceItem.[Product ID]);


George
Click to expand...
Click to expand...
 
John,

The previous info was obtained by "Create Query in Design View".
Selection of all four tables gave this SQL:
FROM Product INNER JOIN ((Customer INNER JOIN Invoice ON Customer.ID =
Invoice.Customer)
INNER JOIN InvoiceItem ON Invoice.ID = InvoiceItem.[Invoice ID])
ON (Customer.ID = Product.ID) AND (Product.ID = InvoiceItem.[Product ID]);

When I "Create Query with Wizard" I get this SQL:
FROM Product INNER JOIN ((Customer INNER JOIN Invoice ON Customer.ID =
Invoice.Customer)
INNER JOIN InvoiceItem ON Invoice.ID = InvoiceItem.[Invoice ID])
ON Product.ID = InvoiceItem.[Product ID];
---

Notice the (Customer.ID = Product.ID) is not included by Wizard.
Why should a different SQL be generated by Wizard?

Altho I selected the tables in normal sequence (Customer, Invoice,
InvoiceItem, Product),
the Wizard places the Product table first (at far left) that causes
confusing relationship lines.
Why does that happen?
Is it related to the way Product is mentioned First in the SQL (by Design
and Wizard)?

George
 
Well, bear in mind: I don't know how your tables are related. Is the
ID field in the Customer table in fact related to the ID field in the
Product table? In a typical many to many relationship, it would not
be: any customer would order multiple products, and any one product
would be ordered by multiple customers.

Have you investigated the Northwind sample database? You may want to
consider using a Form with Subforms, rather than trying to create the
One Great Master Query that does everything!

Comments inline below.
The previous info was obtained by "Create Query in Design View".
Click to expand...

Well, you must either accept the default Joins that the query creates
or change them. If they're wrong, you may have incorrect relationships
defined in the Relationships window.
Selection of all four tables gave this SQL:
FROM Product INNER JOIN ((Customer INNER JOIN Invoice ON Customer.ID =
Invoice.Customer)
Click to expand...

There's a field named Customer in the Invoice table which properly
links to CustomerID? That's OK, but for clarity I'd suggest keeping
the name consistant: CustomerID.
INNER JOIN InvoiceItem ON Invoice.ID = InvoiceItem.[Invoice ID])
ON (Customer.ID = Product.ID) AND (Product.ID = InvoiceItem.[Product ID]);
Click to expand...

Similarly, I'd keep the ID fields named consistantly, and avoid spaces
in fieldnames. The Primary Key of Invoices might better be named
InvoiceID; the foreign key in InvoiceItem would then also be
InvoiceID. Similarly for ProductID.
When I "Create Query with Wizard" I get this SQL:
FROM Product INNER JOIN ((Customer INNER JOIN Invoice ON Customer.ID =
Invoice.Customer)
INNER JOIN InvoiceItem ON Invoice.ID = InvoiceItem.[Invoice ID])
ON Product.ID = InvoiceItem.[Product ID];
Click to expand...

Perhaps because the relationships are set up incorrectly, or it's
guessing at relationships because they're not set up at all? I'm not
sure!
Altho I selected the tables in normal sequence (Customer, Invoice,
InvoiceItem, Product),
the Wizard places the Product table first (at far left) that causes
confusing relationship lines.
Why does that happen?
Click to expand...

Purely graphical. If you don't like how the boxes are laid out on the
screen, use the mouse to select the top bar and move the box where you
like.
Is it related to the way Product is mentioned First in the SQL (by Design
and Wizard)?
Click to expand...

Probably... but again, the graphical layout does not affect the
performance or structure of the query.
 
Does Access allow you to define incorrect relationships?
Click to expand...

Sure. It has no idea what is *LOGICALLY* correct or incorrect for the
real-world situation. If you join a Long Integer ID number to a Long
Integer WidthInCentimeters, Access will comply.
When I open the database, I don't see any error messages.
The queries work OK.
Click to expand...

In that case I'd better go back and reread the thread to see what
problem you're trying to solve!
 
John,
The database I'm using is for a typical "real-world" situation. That's why
the author selected it as a teaching database. There are valid
relationships. The queries work.

Here is the "problem" as I've stated:
I understand the SQL INNER JOIN syntax when two or three tables are
selected.

I do not understand the syntax when all four tables are selected, especially
with Create Query by Design. I've read Access 2000 Help, Jet SQL Reference.

I do not understand why Create Query by Design includes (Customer.ID =
Product.ID), this not being shown in the Relationship screen.

I do not understand why Create Query by Wizard generates different SQL (and
graphics) than Create Query by Design.

What I'm looking for are the rules utilized to generate the SQL based on a
given database with defined relationships. If you were writing a program to
do this, what would it look like? I'm not looking for a long VBA code list
but the logical steps and syntax rules.

Thanks,
George
 
I do not understand the syntax when all four tables are selected, especially
with Create Query by Design. I've read Access 2000 Help, Jet SQL Reference.

I do not understand why Create Query by Design includes (Customer.ID =
Product.ID), this not being shown in the Relationship screen.
Click to expand...

I do not understand it either, and it sounds like an error. I'm
surprised the query works! Surely this would limit the display to show
those customers whose ID happens to match a product?

So... after all this discussion, I fear all I can say is "that's
wierd, I don't know why it does that, and it shouldn't".

My only suggestion would be to open the Relationships window and
select "View All Relationships" and ensure that this erroneous
relationship wasn't in fact inadvertantly added and then hidden:
unlikely but I really can't think of any other explanation.
 
John,

When you had suggested field name changes, I was reluctant to mess with the
sample db.
I tried to "Save As" the sample db to a new db name for testing.
How is that done? (got no clues from Help)

So I created a new db, changed field names and discovered this:

Original db tables Customer, Invoice, and Product have autonumber field name
ID.
If I change Product's field name from ID to ProductID (as you had
suggested), the extra relatonship is not generated by Query Design.

Why should Access care if certain tables have the same field name, or is
there something special about field name "ID"?
And it seems like a bug if Query by Wizard doesn't do the same thing.

Back to my original post question about SQL syntax, which has been avoided.
Is there another place/forum I can get more info on the SQL syntax?
I heard that Access 97 Help file has better info on Jet SQL. Is there a way
to get that?

Thanks,
George
 
Back
Top