You cannot get this kind of result
set with simple queries such as the ones you??™ve used so far. Needing a result set based on data
from multiple tables is a good indication that you might need to use table joins.
When extracting the products that belong to a category, the SQL query isn??™t the same as
when extracting the categories that belong to a department. This is because products and categories
are linked through the product_category linking table.
To get the list of products in a category, you first need to look in the product_category
table and get all the (product_id, category_id) pairs where category_id is the ID of the category
you??™re looking for. That list contains the IDs of the products in that category. Using these
IDs, you??™ll be able to generate the required product list from the product table. Although this
sounds pretty complicated, it can be done using a single SQL query. The real power of SQL lies
in its capability to perform complex operations on large amounts of data using simple queries.
Joining one table with another results in the columns (not the rows) of those tables being
joined. When joining two tables, there always must be a common column on which the join
will be made. Tables are joined in SQL using the JOIN clause. You??™ll learn how to make table joins
by analyzing the product and product_category tables and by analyzing how you can get a list
of products that belong to a certain category.
Pages:
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220