loading..
Русский    English
22:18

Exercise #7 (tips and solutions)

The following solution employs join instead of union:

Console
Execute
  1. SELECT DISTINCT a.model,
  2. ISNULL(b.price, 0)+ISNULL(c.price, 0)+ISNULL(d.price, 0) price
  3. FROM (((Product a LEFT JOIN
  4. PC b ON a.model = b.model
  5. ) LEFT JOIN
  6. Laptop c ON a.model = c.model
  7. ) LEFT JOIN
  8. Printer d ON a.model = d.model
  9. )
  10. WHERE a.maker = 'B';

In this case we employ three left outer joins of Product table with each of production tables.

The missing values of the price will be filled in with NULL values. I.e., for model 1232 of the PC the price of the laptop and the printer will be NULL. Therefore only one of the three price columns will contain the value for each row of the resulting set. In order not to determine which column it is, within the list of columns of SELECT clause the following construction is used

  1. ISNULL(b.price, 0)+ISNULL(c.price, 0)+ISNULL(d.price, 0),
summing up all the three prices, preliminarily substituting NULL value by 0. The latter is necessary, as summing up with NULL value will give NULL. The use in the query of non-standard ISNULL(price, 0) function is not essential, as it is not less efficient to substitute the whole construction with the standard COALESCE, even without summation:

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

And yet the provided solution has one drawback. Just imagine that maker B has got a model, say model 1133, which is not included into production table. Then the result of execution of the query will be the row:

1133 0
with $0 price. Such a result will give fallacious information as there are no products at this price. In order to conform this solution to the solution on the basis of union, which does not output the rows with zero price, one should add the search condition on the price. Do it on your own and check correctness of your solution.

To return to discussion of exercise #7

To solve a problem on SQL-EX.RU

Bookmark and Share
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100