CODEWITHSUNDEEP
phppostgresql
asdf
asdf

Reputation: 39

Query error when getting information from 2 different tables in php

For my homework assignment I have to write queries from a database of information about the world. I get an error in one of my queries and I cant figure out why. The problem statement for the query that I am having trouble with is:

Find all official languages, the country for which it is spoken, and the percentage of speakers      
(percentage of speakers is calculated as percentage spoken times country population divided by 100). 
Order results by the total number of speakers with the most popular language first. (238 results)

The error that I get when I try to run my query from the website is:

Query failed: ERROR: missing FROM-clause entry for table "city" LINE 1: ...kers FROM lab2.country AS 
co JOIN lab2.country ON lab2.city.... ^

The code that I wrote for my query is:

 case 11:
                    $q = "SELECT name, language, ((pecentage * population)/100) AS 
percentage_of_speakers FROM lab2.country AS co JOIN lab2.country ON lab2.city.country_code WHERE  
(is_official IS TRUE) ORDER BY percentage_of_speakers DESC";

                    $result = pg_query($q) or die ('Query failed: '. pg_last_error());
                    break;

The information that I get for this query comes from two different tables instead of one. I believe I have to use a JOIN statement in order to get the data from both tables. Here are the 2 tables that are being used. Thanks for the help in advance.

 Table "lab2.country_language"
Column    |         Type          |               Modifiers                
--------------+-----------------------+----------------------------------------
 country_code | character(3)          | not null default ''::bpchar
 language     | character varying(30) | not null default ''::character varying
 is_official  | boolean               | not null default false
 percentage   | real                  | not null default 0::real

                           Table "lab2.country"
 Column      |         Type          |               Modifiers                
-----------------+-----------------------+--------------------------------------
 country_code    | character(3)          | not null default ''::bpchar
 name            | character varying(52) | not null default ''::character varying
 continent       | continent             | not null
 region          | character varying(26) | not null default ''::character varying
 surface_area    | real                  | not null default 0::real
 indep_year      | smallint              | 
 population      | integer               | not null default 0

Upvotes: 1

Views: 86

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324501

I reformatted your query so I could read it, but I didn't fix the problems. Here's how it looks with better formatting:

SELECT 
  name, 
  language, 
  ((pecentage * population)/100) AS percentage_of_speakers 
FROM lab2.country AS co 
     JOIN lab2.country ON lab2.city.country_code
WHERE is_official
ORDER BY percentage_of_speakers DESC

The problem in the query is the two highlighted parts below:

FROM lab2.country AS co 
     JOIN lab2.country ON lab2.city.country_code
               ^^^^^^^    ^^^^^^^^^^^^^^^^^^^^^^

The first problem is that you're trying to join lab2.country to its self (lab2.country AS co JOIN lab2.country). You're actually allowed to do that in SQL, and it's really useful sometimes, but it's not what you need here. You need to join lab2.country to lab2.city instead.

The bigger problem is the ON expression in your join. You can't just join on a value like that. A join predicate (that's what the part after the ON keyword is called) must be an expression that evaluates to a boolean value (i.e. is true or false). It is what links the two tables together.

To learn how to solve this, I suggest that you study the PostgreSQL tutorial on joins. The linked tutorial provides an example of how this works using the example tables from the PostgreSQL tutorial (these are not the tables in your question):

SELECT *
FROM weather INNER JOIN cities ON (weather.city = cities.name);

See how (weather.city = cities.name) provides a kind of "test" expression that can be run for each combination of rows to see whether they match or not?

Hopefully that'll explain how joins work so you can understand how to fix your original query.

(By the way, I strongly recommend getting used to testing your code interactively in a tool like psql or PgAdmin-III. They're way more convenient than some random web based query tool, and often give better error messages.)

Upvotes: 1

Related Questions