Exercise 39: Define the ships that “survived for future battles”; being damaged in one battle, they took part in another.
Database Schema: http://www.sql-ex.ru/help/select13.php#db_3
My approach:
SELECT distinct o.ship from Outcomes o WHERE o.RESULT = 'damaged' AND exists (select 1 FROM Outcomes o2 WHERE o2.ship = o.ship AND (o2.result='OK' OR o2.result='sunk'))
sql-ex says
Your query produced correct result set on main database, but it failed test on second, checking database
correct result matched with my output.
Where I failed?
Advertisement
Answer
Solved! needed to add Distinct
select distinct kk.ship from (Select ship,date from outcomes oo,battles bb where oo.battle=bb.name) tt inner join (Select ship,date as damagedate from outcomes oo,battles bb where result='damaged' AND oo.battle=bb.name) kk ON tt.ship=kk.ship AND tt.date>kk.damagedate