Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Other subqueries
21. Subqueries in the FROM clause

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;