Compound SQL query

[Froskoy]

Hi,

I'm just starting with SQL and have quite a basic question.

I have a table People with the following fields:
PersonID, Name, CountryID

Sample data:
1 Bob 4
2 Jim 5
3 Bill 5

A Countries table with the following fields:

CountryID, Name, ContinentID

Sample data:
4 USA 1
5 UK 2

And a Continents table with the following fields:
ContinentID, Name

Sample data:
1 North America
2 Europe

Now I want to submit a query that will return the names of all the people in Europe.

I was thinking that this would be along the lines of:

SELECT Name FROM People WHERE CountryID=(SELECT ContinentID FROM Countries WHERE ContinentID=2)

But this doesn't seem to work - where have I gone wrong? With my sample data above the query should return Jim and Bill.

Many, many thanks,

Froskoy.

 

[r937]

SELECT People.Name 
  FROM Continents 
INNER
  JOIN Countries
    ON Countries.ContinentID = Continents.ContinentID
INNER
  JOIN People 
    ON People.CountryID = Countries.ContinentID
 WHERE Continents.Name = 'Europe'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

SELECT P.Name
FROM People P
INNER JOIN Countries C ON P.CountryID = C.CountryID
WHERE C.ContinentID = 2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886