Compound SQL query

Froskoy · Jan 23, 2011

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:

Code:

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 · Jan 23, 2011

Code:

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 · Jan 23, 2011

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Compound SQL query

Froskoy

Programmer

r937

Technical User

PHV

MIS

Similar threads

Part and Inventory Search

Sponsor