Compound SQL query

Question

Compound SQL query

Froskoy (Programmer)

(OP)

23 Jan 11 06:33

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 (TechnicalUser) · Answer 1 · 2011-01-23 07:51:44

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 (MIS) · Answer 2 · 2011-01-23 11:46:50

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Come Join Us!

Posting Guidelines

Compound SQL query