Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Instruction
Great! Of course, you can pick just a few columns in such queries. Study the following example:
SELECT
NiceCity.Name,
Trip.Days,
Trip.Price
FROM Trip
INNER JOIN (
SELECT
*
FROM City
WHERE City.Rating = 5
) AS NiceCity
ON NiceCity.ID = Trip.CityID;
The above query finds trips and their respective cities for those cities which are rated 5. It then shows the Name, Days, and Price columns for these tables. If the tables have all columns with different names, then you may drop the table names (i.e., you can write Price instead of Trip.Price because there is just one column Price anyway).
Exercise
Show hiking trips together with their mountains. The mountains must be at least 3000 units tall. Select only the Length and Height columns.
Stuck? Here's a hint!
Type:
SELECT
HikingTrip.Length,
HighMountain.Height
FROM HikingTrip
INNER JOIN (
SELECT
*
FROM Mountain
WHERE Mountain.Height >= 3000
) AS HighMountain
ON HighMountain.ID = HikingTrip.MountainID;
