Exercise: 39

Find the ships that survived for future battles; that is, after being damaged in a battle, they participated in another one, which occurred later.

Solution

-- solution 1
SELECT DISTINCT t1.ship
FROM (
	SELECT ship, date, result
	FROM outcomes
		INNER JOIN battles ON outcomes.battle = battles.name
	WHERE outcomes.result = 'damaged'
) t1
	INNER JOIN (
		SELECT ship, date, result
		FROM outcomes
			INNER JOIN battles ON outcomes.battle = battles.name
	) t2
	ON t1.ship = t2.ship
WHERE t1.date < t2.date

-- solution 2
WITH t1 AS (
		SELECT ship, date
		FROM outcomes join battles
		ON outcomes.battle = battles.name
                WHERE outcomes.result = 'damaged'
	), 
	t2 AS (
		SELECT ship, date
		FROM outcomes join battles
		ON outcomes.battle = battles.name
	)
SELECT DISTINCT t2.ship
FROM t1 INNER JOIN t2
ON t1.ship = t2.ship
WHERE t1.date < t2.date

References

  1. WITH common_table_expression (Transact-SQL)