Figure 8.5 The relationships between the various tables involved in searching
for a list of products
which database structures are involved with accomplishing this. Figure 8.5 shows
the tables that we will be working with in our examination of Dynamic SQL .
Now let's define which table structures are involved. Three database tables are
involved in fulfilling our requirements (figure 8.5). We are using the Product table
from our previous example. The two new tables introduced in this example are
named Category and Item. Category is a simple table that defines the category that
the product belongs in. The Item table defines the various product permutations.
Writing the SQL in static format
Now that we have defined the inputs and outputs, we can write the SQL using sim-
ple static SQL that we can run in a query tool. Listing 8.11 shows a static SQL state-
ment that we will start with and make into a dynamic statement.
Static SQL mock-up
p.PRODUCTID AS PRODUCTID,
p.NAME AS NAME,
p.DESCRIPTION AS DESCRIPTION,
p.IMAGE AS IMAGE,
p.CATEGORYID AS CATEGORYID
FROM Product p
INNER JOIN Category c ON
INNER JOIN Item i ON
i.productId = p.productId