Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
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;
