Solution 3.12.5
Console
SELECT t1.class, COUNT(*) AS cnt
FROM (SELECT a.class, b.name
FROM Classes a LEFT JOIN
-- join with Ships without head ships:
Ships b ON a.class = b.class AND
a.class <> b.name
) AS t1 JOIN
-- join either by class for head ships or by name:
Outcomes t2 ON t1.class = t2.ship OR
t1.name = t2.ship
WHERE result = 'sunk'
GROUP BY t1.class
-- choose ship classes that absent from the first query.
-- these are classes having no sunken ships.
UNION
SELECT class, '0'
FROM Classes
WHERE class NOT IN (SELECT DISTINCT t1.class
FROM (SELECT a.class, b.name
FROM Classes a LEFT JOIN
Ships b ON a.class = b.class AND
a.class <> b.name
) AS t1 JOIN
Outcomes t2 ON t1.class = t2.ship OR
t1.name = t2.ship
WHERE result = 'sunk'
);
Solution 3.12.6
Console
SELECT d.class class, (SELECT COUNT(f.result)
FROM (SELECT c.result
FROM Ships b LEFT OUTER JOIN
Outcomes c ON (b.name = c.ship)
WHERE c.result = 'sunk' AND
d.class = b.class
UNION ALL
SELECT c.result
FROM Outcomes c
WHERE c.result = 'sunk' AND
d.class = c.ship
) f
) Sunks
FROM Classes d
For the analysis of two last solution - 3.12.5 and 3.12.6 - we shall consider following variants of data. In Ships table (for the purpose of analysis, meaningful columns are only shown):
| name
|
class
|
| ship1_class_1 | class_1 |
| ship1_class_1 | class_1 |
|
In Outcomes table:
| ship
|
result
|
| ship1_class_1 | Sunk |
| class_1 | Sunk |
|
Then according to a join predicate in the solution 3.12.5
ON t1.class = t2.ship OR
t1.name = t2.ship
ship1_class_1 ship from Ships table will fall twice into result set as its name coincides with the name of the ship from the first row in Outcomes table and its class coincides with name of the ship from the second row. As a result we shall receive 3 sunken ships, though actually them only 2.
The task solution 3.12.6 will give us correct result in this case, as the first query in union (join by ship name) will give ship1_class_1 only, whereas the second gives class_1 only. However this solution is not correct also as will be shown on other variant of data.
In Ships table
| name
|
class
|
| ship1_class_2 | class_2 |
| class_2 | class_2 |
|
In òàáëèöå Outcomes:
| ship
|
result
|
| ship1_class_2 | sunk |
| class_2 | sunk |
|
The first query in union will give us the both of sunken ships in the class_2 class, and the second one gives the head ship of this class. As here UNION ALL is used, the head ship will be twice presented in result set; therefore we again receive 3 ships instead of 2. Cosmetic correction that changes UNION ALL by UNION gives not correct solution, because we get the same mistake as in the solution 3.12.4 when for any quantity of the sunken ships of a class only 1 ship is presented in the result set .
By the way, the solution 3.12.5 gives value of 3 on these data also, but for other reason described above.
T&S
To solve the problem on SQL-EX.RU
go
Console
