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:
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:
1133
0
The price will be $0. This result is incorrect, since there are no products in the database at such a price. To make the output of this query match the one of the UNION-based solution that doesn’t display records with zero prices, another condition for filtering by price has to be added. This is left as an exercise to the reader; after that, you can check your solution.
go
Console
