or 02-9262-7770 
| Database Joins | |
By Chris Smith |
Published
03/8/2006
|
Programming
|
Rating:
|
|
|
Introduction to Table JoinsA visitor reading the second part of this tutorial asked what table joins are and how you use them. Because of that, this months tutorial is about exactly that - so remember, if you have a comment or question, make sure you send it through at the end of the article! Setting up multiple tables in a database is an easy way to keep related data together. We keep news separate from authors, blogs separate from news, etc. Not only is this more managable from our point of view (we can quickly see which table contains what information), but all of our data will be in better shape also. If we didn't separate the data then we'd have to keep the author information with the news item and store the blog information separately - it's not practical and very prone to errors. If you change an authors email address in one place, then you might forget to change it somewhere else. Keeping the data separate like this is called "database normalization" and is quite a complicated topic in itself, so we'll leave that alone for now. Back to the topic at hand! In our CMS example from last month we have an authors table that looks like this: CREATE TABLE authors ( authorid INT PRIMARY KEY, username VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255) ); ... and another table for news which is slightly modified from the previous example: CREATE TABLE newsitem ( newsid INT PRIMARY KEY, newstitle VARCHAR(255), newscontent TEXT, authorid INT REFERENCES authors(authorid) ); We need to create the authors table first because the "newsitem" table makes a reference to it and the database should check to make sure it's available so it can set up the relationship. How do we get the authors name for a particular news item? We have to join the tables together. Note that both tables (in this example at least) have a column called 'authorid'. In the 'authors' table this is a primary key so we can uniquely identify each row. In most cases, when you join tables together in a query you should be using a primary key - or at least a unique key - from one of the tables. |
|
