Relational links and joins

Suppose that for each employee, you wanted to keep a record of holidays taken. Such a table might look like:

HolidayID

EmployeeID

StartDate

DaysHoliday

1

1002

1/2/97

5

2

1003

21/2/97

1

3

1002

24/2/97

3

4

1004

1/3/97

4

Note that this table has a reference - EmployeeID - to the Employee table (see Employee table). This is called a Foreign Key and implies some important rules that should be (but are not always) abided by:

  1. Each record in the Holiday table must contain a valid EmployeeID, that is you can’t have an EmployeeID in the Holiday table that doesn’t also have a matching entry in the Employee table.

  2. Each EmployeeID listed in the Holiday table can only exist once in the Employee table. EmployeeID forms a unique index for the Employee table called a primary key. The resultant relation between the Employee table and the Holiday table is one-to-many, that each employee can have zero, one or more holidays.

These rules provide the basis for referential integrity, a goal of good database design that Ability will try to help you achieve.

To join the two tables, use the following statement:

SELECT Employee.*, Holiday.* FROM Employee INNER JOIN Holiday ON Employee.EmployeeID = Holiday.EmployeeID;

This selects all the fields from both tables. To select some of the fields, care has to be taken not to confuse fields from one table with another – for example, the field EmployeeID exists in both tables. To avoid conflicts, tag on the table name to each field in the following manner:

SELECT Employee.EmployeeID, Employee.FirstName, Employee.LastName, Holiday.HolidayID, Holiday.StartDate, Holiday.DaysHoliday FROM Employee INNER JOIN Holiday ON Employee.EmployeeID = Holiday.EmployeeID;

This produces the following results table:

EmployeeID

FirstName

LastName

HolidayID

StartDate

DaysHoliday

1002

Chris

England

1

1/2/97

5

1002

Chris

England

3

24/2/97

3

1003

Andreas

Smith

2

21/2/97

1

1004

Jim

Smith

4

1/3/97

4

See also:

Join types

Unmatched queries

Other types of SQL statements