When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, May 05, 1999

Utilizing the INNER Join


To some people, this may seem like another name for an Alabama marriage (sorry to those I offend, but hey, it's stereotypically funny), yet it is an actuality a very powerful SQL tool. Not many ASP programmers are aware of the full power that you can unleash with properly written SQL statements. Most programmers build a simple database in Access, let that be, and focus on the ASP. Can't figure out how to manipulate this data? Write an ASP routine! Want to combine multiple tables? Write an ASP routine!

- continued -

(Those who practice good data modeling techniques and insure their database is in third normal form, should already be extensively familiar with inner joins. For those who are unfamiliar with what inner joins are, you should also take the time to read the data modeling articles on 4Guys!)

This can, however, lead to some serious problems. One of these problems is performance. Instancing multiple connection/recordset objects can be a heavy hitter on a webserver, especially if the page is accessed frequently. Most high-visit websites can't handle that kind of performance loss. While one way would be to buy a dedicated SQL server, a better option is to write better SQL statements.

Rather than trying to do everything yourself, let the ODBC SQL drivers handle it. Need to combine multiple tables? No Problem. Want data from multiple tables? No problem. Need to sort various fields scattered throughout multiple tables? NO PROBLEM!

Most people aren't aware how to do this. Sure, they might go out, buy a book, but at today's going rate, ASP books are not that cheap. A better alternative is to get on the Internet. This site will hopefully be a first-stop site for information.

Enough rambling though, on to the topic. What are INNER joins you ask? INNER joins allow you to link data from multiple tables into one area. For example, let's say you had the following tables:

Merchandise
MercID AutoNumber
TypeID Number
Description Text

Types
TypeID AutoNumber
ParentID Number
Name Text

Sales
SaleID AutoNumber
BuyerID Number
SellerID Number
MerchandiseID Number

Users
UserID AutoNumber
Name Text
...
...

To get information from all four tables, you would have to create 4 objects, instance some for/next loops with these objects, create a 5th object to store the data, and then be done with it. Yet this way is STUPID!

It is much easier to use INNER joins. For example, let's say we wanted to find out who has bought something that is of the type table, and display their name and the merchandise description of what they bought.

We have to understand what an INNER JOIN looks like. Here's the general syntax

result table = merge table INNER JOIN source table ON merge table.field = source table.field

What this does is for each field that you specified in the source table, replace it in the merge table, and return the merged results or whatever you requested in the statement. However, if you want to specify records of a certain type, you need to specify a where clause.

Here is an example of INNER JOINing:

SELECT *
FROM Merchandise
    INNER JOIN Types ON
        Merchandise.TypeID = Types.TypeID

However, in our case, because we have primary keys defined, we can't really do that. But what if we wanted to find out all purchases bought with a type ID of 1, and return the Merchandise description and the users name. Here's what the statement would look like:

SELECTMerchandise.Description, Users.Name FROMUsers INNER JOIN (Sales INNER JOIN (Merchandise INNER JOIN Types ON Merchandise.TypeID = Types.TypeID) ON Sales.MercID = Merch andise.MercID) ON Users.UserID = Sales.BuyerID WHERE (((Types.TypeID) = 1));

That turns out to be a big honking line. So let's try to understand it, by breaking it into parts (that is, the parts of the INNER JOIN)

The first command which is executed is the text in black. That code is the complete statement, and is the lowest nested paranthesis, therefore, it is executed first. Translation from SQL to English is - Eliminate all records in the Merchandise table where the Merchandise's TypeID is not equal to one the Type's TypeIDs

Next, the navy takes these results, and uses them in doing this: Eliminating all records in the Sales table where the MercID of the Item that was sold is not equal one to the Merchandise ID of the Merchandise table in the results (that is, the Merchandise ID of each item that is of the type ID specified.)

Furtheremore, the green takes those results, and then does this: Eliminate all records in the Users table where the UserID of the UsersTable is not equal to the buyerID of the Sales table from the results.

All these resultant tables are then returned, and the requested objects are turned.

This makes extensive use of nested INNER JOINs. I've explained how they are evaluated, and what they return. Thus, the mystery of advanced inner joins is explained. You can always refer to above for syntax of it, and simple and advanced examples.

One way to imagine INNER JOINs is like relationships. In fact, if you use Access to build your queries, you may have noticed that you can define relationships. These relationships are then created as JOINS (LEFT, RIGHT, INNER). I'll explain the other two in a future lesson, but not for now.

If you need any help with INNER JOINs, feel free to contact me. I'm still learning, but I'm sure I'll be able to help you!

God Bless from Ryan S



This article was written by Ryan S. Ryan has been a computer programmer in the loosest sense since the age of 8. He has been working with ASP since the age of 13, when it first came out (that he knows of), and is somewhat advanced at it.


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article