CODEWITHSUNDEEP
sql
neojakey
neojakey

Reputation: 1663

Nested SQL Count

I have a two tables, Member and Country, what I want to do is find all the countries that have no members. I am kinda lost, here is my best guess, but it doesn't work:

SELECT CountryID
FROM Country 
WHERE (SELECT COUNT(MemberID) FROM Member WHERE PrCountryID = CountryID) = 0

Any help would be greatly appreciated.

neojakey

Upvotes: 2

Views: 196

Answers (3)

garyh
garyh

Reputation: 2852

Without seeing the tables I think you need something like this

select * from Country c
left outer join Member m on m.PrCountryID = c.CountryID
where m.PrCountryID is null

Upvotes: 0

Rollins999
Rollins999

Reputation: 688

select countryID from Country C left join member M on C.CountryID=M.CountryID where M.MemberID is null

Upvotes: 2

Robert
Robert

Reputation: 25753

Try to use not exists clause:

SELECT C.CountryID
FROM Country C
WHERE not exists(SELECT 1 FROM Member M WHERE M.PrCountryID = C.CountryID)

Upvotes: 1

Related Questions