Exercise #7 (tips and solutions)

Here is a solution using JOIN instead of UNION:

SELECT DISTINCT a.model,
ISNULL(b.price, 0)+ISNULL(c.price, 0)+ISNULL(d.price, 0) price
FROM (((Product a LEFT JOIN
PC b ON a.model = b.model
) LEFT JOIN
Laptop c ON a.model = c.model
) LEFT JOIN
Printer d ON a.model = d.model
)
WHERE a.maker = 'B';

In this case we perform three left outer joins, combining the Product table with each of the other tables.

Missing prices will be substituted by NULL values. I.e., for PC model 1232, the laptop and printer prices will be NULL. Therefore, for each record returned by the query, only one of the price columns will contain an actual price. To avoid finding out which one exactly, the  SELECT  statement uses the following construct:

ISNULL(b.price, 0)+ISNULL(c.price, 0)+ISNULL(d.price, 0),

that sums up all three prices replacing NULL values by 0 before doing so. The latter is necessary, since addition with NULL yields NULL. Using the ISNULL(price, 0) function that isn’t a part of the SQL standard is not necessary, since the whole construct can be replaced by the standard COALESCE function that does the job as well, and doesn’t even need adding the prices together:

COALESCE(b.price, c.price, d.price, 0)

And yet this solution has one drawback. Imagine maker B has a model (let it be model 1133) not present in any of the specific product type tables. For this model, the query will return the following row:

Return to discussion of exercise #7

Solve this task at SQL-EX.RU