Tutorials

Data Joins Unions

by Barry Andrew on Jun 30, 2008 5:59:59 PM

Views: 22008

Introduction

In this tutorial we look at using joins and unions in our database queries. Before we get to those, however, we'll first put our data into a format where we can take full advantage of the power of SQL joins. This process of removing redundant duplication and repetition of data is called "normalization". I am not covering it in any detail here but I will give you a rough-and-ready method of handling data relationships. (Google "data normalization" so you know exactly what you are doing. In practice you'll use first, second and third normal forms. Fourth and beyond are reserved for theoretical classroom exercises.)

This tutorial is primarily written for MySQL. Some adjustment to the queries may be required for other dialects of SQL.

The Scenario

Throughout this tutorial we will use a fictitious school database. The school's headmaster, Mr Beetsem-Daley, has commissioned us to build an intranet for pupil administration. He requires us to record which pupils are in each house and the subjects chosen for study by each pupil. His data is currently held in spreadsheet and looks like this:

+------------------+------------+------------------+----------------------------------------+
| pupil_name       | house_name | house_master     | chosen subjects                        |
+------------------+------------+------------------+----------------------------------------+
| Adam Simms       | Laker      | Frank Morrisson  | Economics, English, German             |
| Allan Blair      | Grace      | Charles Hadleigh | Economics, Geography, German, Physics  |
| Anna Hamilton    | Grace      | Charles Hadleigh | English, History                       |
| Anne Bailey      | Jardine    | Pauline Fforbes  | English, German, History, Physics      |
| Anthony Bell     | Cowdrey    | Robert Bingley   | Economics, Geography, History, Physics |
| Caroline Freeman | Grace      | Charles Hadleigh | Biology, Chemistry, Geography, History |
| David Powell     | Laker      | Frank Morrisson  | Economics, Maths, Physics              |
| Emma Watson      | Cowdrey    | Robert Bingley   | English, Geography                     |
| Gearge Wilson    | Laker      | Frank Morrisson  | Chemistry, Economics, English, History |
| Henry Irving     | Cowdrey    | Robert Bingley   | Biology, Chemistry, Maths              |
| Jane Morrison    | Laker      | Frank Morrisson  | Economics, English, Physics            |
| John Patterson   | Jardine    | Pauline Fforbes  | Geography, German                      |
| John Tully       | Jardine    | Pauline Fforbes  | Biology, English, History,Maths        |
| John Watson      | Grace      | Charles Hadleigh | Chemistry, Economics, English, German  |
| John Williams    | Grace      | Charles Hadleigh | English, German, History               |
| Margaret Norton  | Cowdrey    | Robert Bingley   | German, Physics                        |
| Mary Blake       | Cowdrey    | Robert Bingley   | German, History, Physics               |
| Mary Sheldon     | Jardine    | Pauline Fforbes  | Chemistry, Economics, English, History |
| Mary Whitehouse  | Grace      | Charles Hadleigh | English, Geography, History, Maths     |
| Michael Grove    | Jardine    | Pauline Fforbes  | Economics, English, Physics            |
| Peter Adamson    | Laker      | Frank Morrisson  | Chemistry, Economics, German, Physics  |
| Peter Appleby    | Jardine    | Pauline Fforbes  | Biology, Economics, German, Maths      |
| Wayne Jones      | Laker      | Frank Morrisson  | Biology, Economics, German, Maths      |
| William Smith    | Cowdrey    | Robert Bingley   | Biology, Economics, Maths, Physics     |
+------------------+------------+------------------+----------------------------------------+

Instantly we notice the repetition of the house and house-master names and the totally unsuitable comma-separated lists.

Comments

Very useful!
My php for dummies was useless for joins - many thanks!

1. Tom Butterworth on Jul 1, 2008 6:20:06 PM

Barand, could you extend the tutorial to include how to loop the results of the query. I find that when I use a Join , the query will return the first result and then stop. It will not run thru both tables and list all the results. When the 1st match is found, the query then doesnt look thru for other matches which seems to mean that the if stmt following the result is not running correctly .

Ex: $sql11 = "SELECT * FROM `TabA`
INNER JOIN
`TabB` USING (Sid)
WHERE TabA.Eid = $group AND TabA.Month = $month AND TabA.Year = $year ORDER BY TabB.Name asc" ;

$result11 = mysql_query($sql11);
if($myrow11 = mysql_fetch_array($result11))
{ do whatever}
while($myrow11 = mysql_fetch_array($result11);

Thanks !

2. swatisonee on Jul 3, 2008 4:07:11 AM

swatisonee: You'd do it like with all other query results:

3. Daniel Egeberg on Jul 3, 2008 5:45:05 AM

Thanks Daniel but I'm afraid I still fail to run thru the loop. I will post on the help forum the code as it runs without using Join and then the one I am working on to replace it using Joins as it is too long to post as a tute. comment.

4. swatisonee on Jul 4, 2008 4:38:28 AM
Login or register to post a comment.