by
Brian Haveri aka bwh2 |
2 October 2006
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,
tbl_works_categories,
tbl_categories
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!
|
Relational Database Design - Page 5
