Skip to content
Advertisement

sql-ex exercise 39 failed on second server error?

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
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement