CODEWITHSUNDEEP
sqlcountinner-join
Roberto Xocop
Roberto Xocop

Reputation: 21

Explicit join operations in SQL

I'm working on this query

Determine names of all the ships in the Ships table, which may be a linear (battle) Japanese ship having at least nine main guns with caliber less than 19 inches and displacement not more than 65 000 tons.

The correct result is :

Musashi,
Yamato

my query is:

select   
    name
from 
    ships A
inner join 
    classes B on A.class = B.class
where 
    B.class = ANY (select class 
                   from classes
                   where country like 'Japan')
    and (numguns >= 9 and bore < 19)

and my result is right. But the application throws me the error:

Your query returned the correct dataset on the first (available) database, but it returned incorrect dataset on the second checking database. * Wrong number of records (less by 7)

database schema : http://img1.imagilive.com/0315/Snap_23-03-2015_at_151544.jpg

Upvotes: 1

Views: 926

Answers (2)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36621

SELECT distinct S.name
FROM   Ships as S INNER JOIN Classes as C
ON     S.class = C.class
WHERE  (C.country = 'Japan' or C.country is null)
and (C.type = 'bb' or C.type is null)
and (numGuns >= 9  or numGuns is null)
and (bore < 19  or bore is null)
and (displacement <= 65000  or displacement is null)

Upvotes: 0

Richard Lu
Richard Lu

Reputation: 71

In this case, "May Be" means any of the search conditions could be NULL which is needed to be considered

select name from 
 (select name, s.class, numguns, bore, displacement, type, country
  from classes c, ships s where c.class=s.class) a

where (country='japan' or country is null) 
and (numguns>=9 or numguns is null)     
and (bore<19 or bore is null) and (displacement<=65000 or displacement is null) 
and (type='bb' or type is null)

Upvotes: 1

Related Questions