Ok... here it is...

DECLARE @SpName varchar(10)

SET @SpName = 'Y'

SELECT DISTINCT ANTI_MISSILE.antim_id,ANTI_MISSILE.anitm_name FROM DEFUSE_CAPABILITY

INNER JOIN ANTI_MISSILE ON DEFUSE_CAPABILITY.antim_id = ANTI_MISSILE.antim_id

WHERE DEFUSE_CAPABILITY.antim_id IN

(

SELECT antim_id FROM

(

SELECT Count(*) AS xcnt ,antim_id FROM DEFUSE_CAPABILITY

WHERE missile_id IN (SELECT missile_id FROM ALIEN_CAPABILITY INNER JOIN SPECIES ON SPECIES.spcid = ALIEN_CAPABILITY.spcid WHERE SPECIES.spcname = @SPname)

Group BY antim_id

) C1

WHERE xcnt >= (SELECT Count(*) FROM ALIEN_CAPABILITY INNER JOIN SPECIES ON SPECIES.spcid = ALIEN_CAPABILITY.spcid WHERE SPECIES.spcname = @SPname)

)

Atif Sheikh