Microsoft Office Tutorials and References
In Depth Information
In the query designer in Figure 4-21, we have already selected the three tables: Product,
ProductSubcategory, and ProductCategory. Moreover, because we want to spend some
attention on the Relationship pane, we have expanded it to make it more visible.
FIguRE 4-21 The SQL query designer.
Let us explore the query designer in greater detail. In the left pane, you can select the tables
to add to the query and, expanding each table, you can select the single columns that you
want to retrieve. In the right part of the window, you can see the list of all the columns selected
(which is the SELECT part of the query), the list of the tables involved, along with their
relationships (that is the FROM part of the query), and in the lower part, the list of filters to apply
(that is the WHERE part).
In the example, we selected a small number of columns, to it the screenshots in the topic. In
the real world, the number of columns to retrieve is normally much higher.
The less intuitive part of the query designer is the Relationship pane. You can see that after
you selected three tables, the designer detected the existence of relationships between them:
one between Product and ProductSubcategory and one between ProductSubcategory and
ProductCategory. Those relationships have been automatically detected by the engine because
they are stored in the database metadata. Later on, we discuss how to define new relationships
if the automatic detection algorithm does not find suitable ones.
To understand how relationships work, we need to make some notes:
■ A relationship exists between two tables only. It does not matter how many tables are
involved in the query, a single relationship relates only two of them at a time.
■ The two tables are ordered: one is called Left table and the other one is, fantastically,
the Right table . This is very important, as we see in the next paragraphs, because a
wrong table ordering might lead to an incorrect query.