Relational Database Design - Page 5
       by Brian Haveri aka bwh2 | 2 October 2006

Joining Tables
At this point, the database is set up to hold our works and the corresponding categories. Because the data is spread across three tables, we need to join the tables within our SELECT query. Joining tables means that we combine tables based on common fields (related fields). Joins do not physically join the tables, but rather join the query result sets from implied queries. If that confused you, don't worry. Just know that there is no physical change to the database design.


SQL professionals typically use all capital letters to denote reserved words and functions such as SELECT, INSERT, JOIN, ORDER BY, etc.

Gets the category names associated with work_id #1
Should return 1 row with the category_name = 'Websites'
SELECT c.category_name
FROM tbl_works a,
tbl_works_categories b,
tbl_categories c
WHERE a.work_id = 1
AND a.work_id = b.work_id
AND b.category_id = c.category_id

If a, b, and c look confusing in the above query, don't be alarmed. In the above, a, b, and c are just alias table names. In essence, a, b, and c just represent a shorter way to type the table names that are used later in the query. The above query is the same as typing the following:

Functions identically to the above query
Gets the category names associated with work_id #1
SELECT tbl_categories.category_name
FROM tbl_works,
WHERE tbl_works.work_id = 1
AND tbl_works.work_id = tbl_works_categories.work_id
AND tbl_works_categories.category_id = tbl_categories.category_id

If we look at the entity relationship diagram, we can follow the query through the linking fields. Some call tables like tbl_works_categories linking tables (or mapping tables) because they link together (map) other tables like tbl_works and tbl_categories.

Onwards to the next page!

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8

SUPPORTERS:'s fast and reliable hosting provided by Media Temple.