Exercise #24 page 2
Let`s consider one else method without using UNION, although it`s wrong. This solution uses join of all models and then selects variants by operator CASE.
The Cartesian product of three tables is using in the FROM clause. Then rows contain models of each type of production with maximal price in its production category are selecting in WHERE clause. Excessiveness of the result isn`t wrong (excessiveness occures for instance in case when two models in each table have maximal price, in this case the result will have eigth rows — 2*2*2), because duplicates will be removed by DISTINCT option subsequently.
Next, models with global maximal price are selecting in CASE operator. Mistake is consealed here. The peculiarity of working of operator CASE is in sequentially checking of WHEN clauses. That`s why when condition will became true first, the corresponding THEN clause value will be returned, and the inspection of further WHEN clauses will not be executed.
Let`s consider the next data variant from this point. Let the models of printer and PC has maximal prices. Then the first WHEN clause of CASE operator will be true:
Both predicates are true, indeed. The query returns the model of PC only. To be more precise, the result returns all models of PC with maximal price.
Try to correct this solution without using UNION operator.