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:
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.
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: