CREATE TABLE SPECIES(
  spcid INTEGER primary key,
  spcname VARCHAR(100)
)
GO

CREATE TABLE ALIEN_MISSILE(
  missile_id INTEGER primary key,
  missile_name VARCHAR(100),
  descr VARCHAR(400)
)
GO

CREATE TABLE ALIEN_CAPABILITY(
  spcid INTEGER,
  missile_id INTEGER,
  constraint fk_alien_capability_species foreign key (spcid) references SPECIES(spcid),
  constraint fk_alien_capability_alien_missile foreign key (missile_id) references ALIEN_MISSILE(missile_id)
)
GO

CREATE TABLE ANTI_MISSILE(
  antim_id INTEGER primary key,
  anitm_name VARCHAR(100),
  anitm_descr VARCHAR(400)
)
GO

CREATE TABLE DEFUSE_CAPABILITY(
  antim_id INTEGER,
  missile_id INTEGER,
  constraint fk_defuse_capability_anti_missile foreign key (antim_id) references ANTI_MISSILE(antim_id),
  constraint fk_defuse_capability_alien_missile foreign key (missile_id)  references ALIEN_MISSILE(missile_id)
)
GO

SET NOCOUNT ON
INSERT INTO SPECIES (spcid, spcname) VALUES (1, 'X')
INSERT INTO SPECIES (spcid, spcname) VALUES (2, 'Y')


INSERT INTO ALIEN_MISSILE(missile_id, missile_name, descr) VALUES (1, 'M-1', 'Missile 1')
INSERT INTO ALIEN_MISSILE(missile_id, missile_name, descr) VALUES (2, 'M-2', 'Missile 2')
INSERT INTO ALIEN_MISSILE(missile_id, missile_name, descr) VALUES (3, 'M-3', 'Missile 3')
INSERT INTO ALIEN_MISSILE(missile_id, missile_name, descr) VALUES (4, 'M-4', 'Missile 4')


INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (1, 1)
INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (1, 2)
INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (1, 3)

INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (2, 1)
INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (2, 4)


INSERT INTO ANTI_MISSILE(antim_id, anitm_name, anitm_descr) VALUES (1, 'AM-1', 'Anti-missile 1')
INSERT INTO ANTI_MISSILE(antim_id, anitm_name, anitm_descr) VALUES (2, 'AM-2', 'Anti-missile 2')
INSERT INTO ANTI_MISSILE(antim_id, anitm_name, anitm_descr) VALUES (3, 'AM-3', 'Anti-missile 3')
INSERT INTO ANTI_MISSILE(antim_id, anitm_name, anitm_descr) VALUES (4, 'AM-4', 'Anti-missile 4')


INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (1, 1)
INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (1, 2)
INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (1, 3)
INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (1, 4)

INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (2, 1)
INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (2, 2)

INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (3, 1)
INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (3, 2)
INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (3, 3)

INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (4, 1)
INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (4, 2)
INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (4, 4)

GO

DECLARE @spcname varchar(50)
SET @spcname='Y'

SELECT b.antim_id,anitm_name
FROM
(SELECT a.spcid,a.missile_id,b.spcname
FROM Alien_CAPABILITY a INNER JOIN SPECIES b
ON a.spcid=b.spcid
WHERE spcname=@spcname) a
INNER JOIN DEFUSE_CAPABILITY b
ON a.missile_id=b.missile_id INNER JOIN ANTI_MISSILE c
ON b.antim_id=c.antim_id
GROUP BY b.antim_id ,anitm_name
HAVING count(*)=(SELECT count(*) FROM Alien_CAPABILITY a INNER JOIN SPECIES b ON a.spcid=b.spcid WHERE spcname=@spcname)

well ,goce you just seem to enjoy criticism....you might as well answer the question for bishu.....i guess that will help more ...

I agree that he should have posted this puzzle somewhere else but after pointing that you might as well help him solve that...

Okay...goce that was so nice of you to give the whole script...that save much time

Thanks

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)
) 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here