I will be testing the code posted previously in a new web site, even though it was copy-pasted from a working site.
Apparently the comma in a string statement caused the display program to freak out and go red for most of the remaining text. I regret this because I wanted you to have the benefit of color coding.
Regarding the INNER JOIN statement..
"SELECT u.id AS id, u.quantity AS quantity, c.customername AS name, c.customerbillingstreetaddress AS address, c.customerbillingcity AS city, c.customerbillingstate AS state, c.customerbillingzipcode AS zipcode, r.productname AS productname, r.price AS price FROM Test3_Purchases u INNER JOIN Test3_Customers c ON c.customercode=u.customercode INNER JOIN Test3_Products r ON r.productcode=u.productcode"
..yes, it is an anaconda but hopefully it will look less daunting when presented in smaller pieces
"SELECT u.id AS id,
u.quantity AS quantity,
c.customername AS name,
c.customerbillingstreetaddress AS address,
c.customerbillingcity AS city,
c.customerbillingstate AS state,
c.customerbillingzipcode AS zipcode,
r.productname AS productname,
r.price AS price
FROM Test3_Purchases u
INNER JOIN Test3_Customers c
ON c.customercode=u.customercode
INNER JOIN Test3_Products r
ON r.productcode=u.productcode"
It has the format SELECT ... FROM ....
Because we are doing interjoins, we use the variables c, r and u to represent Test3_Customers, Test3_Products and Test3_Purchases respectively
c <--> Test3_Customers
r <--> Test3_Products
u <--> Test3_Purchases
I prefer to have the single letter match the first letter of the table name but this time we had Products and Purchases so I went with the second letter.
When an INNER JOIN is created, the chosen letter is placed after the table name with no punctuation code
example: Test3_Customers c INNER JOIN Test3_Purchases u
but when used with a field name it is placed before the field name and there is a period between the letter and the field name
example: c.customername, p.productname
Yes, you will want to have this written down until you've used it a few times
The word AS is used to associate a specific field in a specific table with a general word that will be put in a reader
(I'm not certain but I THINK if you put a.name in the reader then you don't need the 'AS name' part)
If you want a reader to see an id, well, every table has a field id so which one?
c.id AS id tells the computer computer that the general code 'id' specifically refers to the id located in the table designated by c (in this case Test3_Customers)
Often the general code is identical to the field name (r.productname AS productname) but this isn't required. We shortened customerbillingname to name in
c.customerbillingname AS name, both for brevity and also to have the example show that they don't have to be the same.
The word ON and an equality such as r.productname=u.productname comes after an INNER JOIN
Example: alpha a INNER JOIN beta b ON a.something=b.something (something is a field common to table alpha and table beta)
Finally, the general structure if you have multiple joins is:
alpha a INNER JOIN beta b ON a.something=b.something INNER JOIN gamma g ON b.algo=g.algo INNER JOIN delta d ON g.etwas=d.etwas
Notice that additional joins are added by giving a new table name with defining letter followed by defining where the match occurs
It wasn't necessary for the fields to have identical names (you might call a field 'productname' in one table and just 'product' in another). However, I recommend that when you create tables, you go out of your way to make certain that fields to be joined later have identical names to make it easier for you.
In the example table alpha joined to table beta, table beta joined to table gamma, table gamma joined to table delta. This wasn't necessary. In fact, I'm thinking that in the next example, which will be bigger, the table for purchases will be the "hub" of the joins, with several joins to it (thus the example would join beta to alpha, join gamma to alpha and join delta to alpha.
One crucial fact: when there is a join, the two fields join must be typed identically (e.g. joining an int field to an int field, joining a price field to a price field, joining an nvarchar(15) to an nvarchar(15), etc.) It is important that you think about joins as you design your tables on paper before you create them.
-Larry
I may have pushed to much or pushed to far in this instance. My goal was that you would see the use of INNER JOIN, ON, single alphabet letters designating tables, use of a textbox control to send data to the code behind page (and from there to table in the database using SQL). I can add smaller examples (and will be working on some for the web site.) When I started with this I had done some work with table joining using Lotus Approach and I saw some things in video tutorials--perhaps someone who hasn't seen these things would find INNER JOIN to be more of a challenge.
Alternately, if things clicked and you want me to push further, my next step might be to do something that generates dynamic controls. Example: a customer logs in who has utilized various "ship to" addresses in the past. We appreciate that this customer buys things for themself, for their children, for their grandchildren, so we want them to just have a click button next to each possible ship to address, and to have a section where they can add yet another send to address (buy! buy! buy! : - )