Winnipeg Web Design

How-To MySQL Table Joins

Databases are a crucial part of any website these days.  If you surf around the net you will not find many static .html pages on the web.  Sure there are some, but most of the time there will be some dynamic functionality.  This functionality gets its data from a database 9 times out of 10.  Another almost obsolete way of storing data is text files.  Text files are a pain in the ass, and those they have dealt with them can relate.

My database of choice at the current time is MySQL.  This is not saying that it is the best out there, as MSSQL is out there as well.  It is important however to be able to adapt to whichever database the client supports.  MySQL is open-source, free, and extremely fast, which is why I tend to favour it.  Now let’s move onto the topic of joins.

Table joins is probably one of the tougher things to learn in regards to databases.  It is definitely a topic I struggled with early on in my web development career.  I remember creating these big, ugly tables that really should have been split into 3 or 4 different tables.  The reason for this was that I didn’t really have the knowledge of how I could join 2 tables correctly.  I hope to explain this clearly for those users that want to grasp this important topic.

Let’s say we are doing some kind of shopping cart application.  People will need to sign-up to purchase our products.  We will then process our orders as they are in the database.

First of we need to think of all the “things” we need to keep track of.   Below is the 3 I came up with for now:

• users
• orders
• products

Now we need to figure out the relationship between them all.  Draw a box for each of the above and put its appropriate label inside the box, with some spacing between each.  We will now draw lines between the tables which are related to each other.  Do users and orders relate?  Yes they do, so draw a line.  Orders and products also need a line.  Do users relate to products? Yes, but only through the orders, so no line is needed.

Next we need to determine the cardinality of the 2 relationships we have.  Ask the following questions:

• For every user how many orders can they have? Many (put an “m” next to orders on the user/orders line)
• For every order how many users can it have? One( put a “1″ next to users on the user/orders line)
• On to the next line, for every order how many products can it have? In this case we will say 1
• For every product how many orders can it have? Many

Now we can determine that “m” means the table will have a foreign key of the table on the other end of the line, and a “1″ means that the table will contain the primary key.  Using this theory the users and products tables will have a primary key, and the orders table will have the primary keys of these 2 tables within it.  Below is what we end up with:

CREATE TABLE users
(
    user_id int primary key auto_increment,
    firstName char(50),
    lastName char(50)
);

CREATE TABLE orders
(
    order_id int primary key auto_increment
    user_id int,
    product_id int,
    quantity int,
    orderedAt timestamp,
);

CREATE TABLE products
(
    product_id int primary key auto_increment,
    productName char(50)
);

We have now setup a successful structure by which we can get information from all 3 tables with ease.  Let’s setup a scenario where we want to get the product and user associated with an order_id of 23.  The following is what we get:

SELECT firstName, lastName, productName, quantity
FROM users,orders,products
WHERE users.user_id=orders.user_id
AND orders.product_id=products.product_id
AND order_id=23

Now let me explain this.  After the SELECT we list the columns we want to display.  Then in the FROM we list the tables we want to join together.  Followed by a WHERE clause where we specify the common column that we wish to join on.  We have then completed the join and have gotten the information we have needed!

Hopefully this helps clear up this topic for you!

Be Sociable, Share!

• 

Related posts:

1. Fix table-cell and max-width bug in IE8
2. MSSQL vs. MySQL
3. CSS or Tables for layout?

Written by Brenley Dueck