Exercise: 46

For each ship that participated in the Battle of Guadalcanal, get its name, displacement, and the number of guns.

Solution

SELECT ship, displacement, numGuns
FROM (
	SELECT ship, battle, 
    -- if class is null then ship
    COALESCE(class, ship) AS class
	FROM outcomes
		LEFT JOIN ships ON outcomes.ship = ships.name
	WHERE battle = 'Guadalcanal'
) t
	LEFT JOIN classes ON t.class = classes.class

References

  1. COALESCE (Transact-SQL)

  2. SQL FULL OUTER JOIN Keyword