Compound SQL query
Compound SQL query
(OP)
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:
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.
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.
RE: Compound SQL query
CODE
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
RE: Compound SQL query
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?