Dealing with Database - Joining Tables

Last week I set you a little task - creating two tables, each with 10 records and then running a query on them. What results did you get? You should have got 100 records but doesn't this seem rather excessive for two tables with only 10 records each in them? What is going on here?

The problem here is the way that the system "joins" two or more tables. This "joining" is crucial to databases and their design so I want to spend some time on this. For those that didn't do the example (and why not!), here is a simplified version so that you can see the problem.

Imagine I have the following two tables - FIRSTNAME and SURNAME. They contain these values...

FIRSTNAME

Fred Mary Chris

SURNAME

Smith Brown Jones

With no correct join information, the system will start with the 1st table - FORENAME and create a record for each value found in SURNAME. So we would end up with...

Fred Smith Fred Brown Fred Jones

The system will then take the second record of FORENAME and join with each record of SURNAME creating:

Mary Smith Mary Brown Mary Jones

Can you see the trend? Finally we will end up with:

Chris Smith Chris Brown Chris Jones

A total of 9 records. The effect of an incorrect join is to take the number of rows in the first table and multiply it by the number of rows in the second table. Why? Because the system has no way of knowing that the REAL values we want are:

Fred Smith Mary Brown Chris Jones

So how do we tell the system that we want to join the two tables correctly? How is it going to know?

This is where "keys" come into play. A key is an item of data that forms a link with another table. Taking our FORENAME and SURNAME tables, we could define them as follows:

FORNAME

FIRSTNAME EMPLOYEE_NUMBER

And for the Surname table...

SURNAME

SURNAME EMPLOYEE_NUMBER

Create these two tables and enter the following data into each.

FORENAME Table

FIRSTNAME EMPLOYEE_NUMBER

Fred 1

Mary 2

Chris 3

SURNAME Table

SURNAME EMPLOYEE_NUMBER

Smith 1

Brown 2

Jones 3

Now we have a way of joining these two tables together to get the results we want because we can say...

"Take the first record (Fred) and give me all the records on SURNAME where the EMPLOYEE_NUMBER on FORENAME is the same as the EMPLOYEE_NUMBER on SURNAME"

Repeat with the next record on FORENAME and continue until there are no more records in FORENAME.

OK - go through the list of names applying the above test to each record in FORENAME and you get the 3 records we want. The only record on SURNAME that has the same EMPLOYEE_NUMBER, as Fred's record (1) is the Smith record -ergo - Fred Smith. Mary matches with Brown and Chris matches with Jones.

Go To Page: 1 2

Articles in this Topic    Discussions in this Topic