I am having some trouble with queries 6 and 8 (below).
Query 6: I haven't figured out how to get the name of the city with the largest population. (everything else works)
What I need it to do: For each country, list the largest population of any of its cities and the name of that city. Order the results alphabetically by country.
Gives error: Query failed: ERROR: missing FROM-clause entry for table "city" LINE 13: ON (city.population = sq.pop) ^
Query 8: I haven't figured out how to get the capital name. (everything else works)
What I need it to do: For each country with 5-10 languages, list the number of languages spoken, in descending order by number of languages as well as the name of the capital for that country.
Gives error: Query failed: ERROR: missing FROM-clause entry for table "city" LINE 13: ON (country.capital = city.id) ^
(I'm not asking for a complete solution to the queries - just an idea to fix the code I have)
It seems like I need two inner joins on both of them, but maybe something is wrong with my syntax.
Are two inner joins in a single query acceptable?
Is there a syntax error? (I have checked the online documentation, and it seems okay. But maybe I am missing something).
Is there a better way to attempt this than two inner joins?
Here is the relavent snippet of the code: (all other queries and related code works properly)
elseif($_POST["query"] == "6") //need help getting name of city with max pop (everything else prints fine) { $query = "SELECT country.name AS country, largest_city, sq.pop AS population FROM lab6.country INNER JOIN (SELECT MAX(city.population) AS pop, country_code FROM lab6.city GROUP BY country_code) AS sq USING (country_code) INNER JOIN (SELECT city.name AS largest_city FROM lab6.city) AS sq1 ON (city.population = sq.pop) ORDER BY country.name ASC"; } elseif($_POST["query"] == "8") //need help getting capital name (everything else prints fine) { $query = "SELECT country.name, capital, lang_count FROM lab6.country INNER JOIN (SELECT count(language) AS lang_count, country_code FROM lab6.country_language GROUP BY country_code) AS sq USING (country_code) INNER JOIN (SELECT city.name AS capital, city.id FROM lab6.city) AS sq1 ON (country.capital = city.id) ORDER BY lang_count DESC, capital DESC"; } This was solved. Here is the code that worked.
elseif($_POST["query"] == "6") { $query = "SELECT country.name AS country, largest_city, sq.pop AS population FROM lab6.country INNER JOIN (SELECT MAX(city.population) AS pop, country_code FROM lab6.city GROUP BY country_code) AS sq USING (country_code) INNER JOIN (SELECT city.name AS largest_city, city.population FROM lab6.city) AS sq1 ON (sq1.population = sq.pop) ORDER BY country.name ASC"; } elseif($_POST["query"] == "8") { $query = "SELECT country.name, sq1.capital AS capital, lang_count FROM lab6.country INNER JOIN (SELECT count(language) AS lang_count, country_code FROM lab6.country_language GROUP BY country_code) AS sq USING (country_code) INNER JOIN (SELECT city.name AS capital, city.id FROM lab6.city) AS sq1 ON (country.capital = sq1.id) ORDER BY lang_count DESC, capital DESC"; } 
