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";
}
