Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Other subqueries
20. Subqueries in the FROM clause

Instruction

Great! Of course, you can pick just a few columns in those queries. Study the following example:

SELECT
  name,
  days,
  price
FROM trip, (
    SELECT
      *
    FROM city
    WHERE rating = 5) AS nice_city
WHERE nice_city.id = trip.city_id;

The above query finds trips and their respective cities for such cities which are rated 5. It then shows the columns name, days, price for these tables. When 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 3,000 high. Select only the columns length and height.

Stuck? Here's a hint!

Type:

SELECT
  length,
  height
FROM hiking_trip, (
    SELECT
      *
    FROM mountain
    WHERE height >= 3000) AS high_mountain
WHERE high_mountain.id = hiking_trip.mountain_id;