Simple SQL query..cannot get it resolved

I have simple app where are two tables: Enemies and Weapon. Every Weapon must belong to some Enemy. Therefore Weapon has a column ReferenceID that references EnenyID. I can easily check how many enemies do have a weapon:

SELECT COUNT(*) FROM Weap开发者_如何学JAVAons JOIN Enemies ON Weapons.RefID=Enemies.ID

But how can I find out vice versa how many enemies do not have any weapon associated? I.e., how many Enemies here are whose ReferenceID is not used by any Weapon?

SELECT Count(*) 
FROM Enemies 
LEFT JOIN Weapons ON Weapons.RefID = Enemies.ID 
WHERE Enemies.ID IS NULL

SELECT COUNT(*)
FROM Enemies
WHERE NOT EXISTS (SELECT * FROM Weapons WHERE Weapons.RedID=Enemies.ID)

Try with:

SELECT * FROM Enemies LEFT JOIN Weapons ON Weapons.RefID=Enemies.ID HAVING count(weapons.id) = 0

try

select count(*) from Enemies where Enemies.ID not in (Select RefId From Weapons)

Select Count(*) 
From Weapons 
WHERE Not Exists (Select * From Enemies Where Weapons.RefID = Enemies.ID)

Enemies that do not have a weapon associated can be accomplished by:

SELECT  *
FROM    Enemies
LEFT JOIN Weapons ON Enemies.ID = Weapons.RefID
WHERE   Weapons.ID IS NULL

It might be of note that your original query might not give you what you want in that if two weapons are associated to a single enemy you might get duplicates when counting, another possibility for your original query might be:

SELECT  DISTINCT Enemies.ID
FROM    Weapons 
JOIN    Enemies ON Weapons.RefID=Enemies.ID

Select count(*)
From enemies
Where id not in (select referenceid from weapons)