04:07

# Exercise #46 (tips and solutions)

Example of automatic join of two solutions promised in 3.1 accounting one of two moments leads to mistakes, can look like this (read comments).

Console
Execute
`-- Ships which had taken part in the battle of Guadalcanal and are present in the Ships table.-- Take note of using the correlative subquery in the WHERE clause-- which solve problem of removing duplicates in Cartesian product. SELECT a.ship, displacement, numGunsFROM (SELECT ship  FROM Outcomes  WHERE battle = 'Guadalcanal' ) AS a, Classes WHERE class IN (SELECT class  FROM Ships  WHERE name = a.ship )UNION-- The query is analogous to one which selects lead ships from-- Outcomes table which had actioned in battle of Guadalcanal.SELECT a.ship, displacement, numGunsFROM (SELECT ship  FROM Outcomes  WHERE battle = 'Guadalcanal' ) AS a, Classes WHERE class IN (SELECT ship  FROM Outcomes  WHERE ship = a.ship )UNION-- In essence this is the solution in 3.1.1SELECT a.ship, displacement, numGunsFROM (SELECT ship  FROM Outcomes  WHERE battle = 'Guadalcanal' ) AS a LEFT JOIN  Classes ON a.ship = class;`

The excessive rows like below will be return as a result:

ship displacement numGuns
California 32000 12
California NULL NULL

It`s possible to complicate this query more (and make it less efficient), by adding code for exception the wrong row. The presence of NULL-value in displacement column may be a criteria for exception, if there is one else row with the same ship`s name. However, we advice to manage without this solution and solve task by another way. It is possible, and you can make certain of it by visiting cite`s forum.

In conclusion let`s note almost right solution.

Console
Execute
`SELECT name, displacement, numGunsFROM Classes, ShipsWHERE Classes.class = Ships.class AND  name IN (SELECT Ship  FROM Outcomes WHERE battle = 'Guadalcanal' )UNION SELECT class, displacement, numGunsFROM ClassesWHERE class IN(SELECT ship  FROM Outcomes WHERE battle = 'Guadalcanal' );`

The first query from union finds information about ships which is present in the Ships table and had taken part in the battle of Guadalcanal. The second one finds required lead ships in the Outcomes table. The potential duplicates (lead ship is also present in the Ships table) excepts by using UNION clause.

So what`s wrong with this solutuion? If it`s no clear for you, go back to item 3.1 task`s discussion.

To solve a problem on SQL-EX.RU