Writing query for multiple tables in php

I am writing my last query for my homework, but I am stuck on it right now. This query requires me to take information from 2 tables instead of 1. I am confused on how to get this information from both tables and how to put them together. Here is the description of the query that I am trying to write.

Find the name, independence year, and region of all countries where English is an official language.
Order results by region ascending and alphabetize the results within each region by country name.   
(44 results)

Here are the tables that I am going to use for this query

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
Indexes:
 "country_language_pkey" PRIMARY KEY, btree (country_code, language)
Foreign-key constraints:
"country_language_country_code_fkey" FOREIGN KEY (country_code) REFERENCES country(country_code) ON    DELETE CASCADE

 => \d country
                           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
life_expectancy | real                  | 

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

As mentionned in the above comment, you have to do an SQL (inner) join in this case. So you’ll have something like:

SELECT name, indep_year, region 
FROM lab2.country 
JOIN lab2.country_language
ON lab2.country.country_code = lab2.country_language.country_code
WHERE …

Solution 2

A good starting point would be the PostgreSQL tutorial, in particular, joins between tables.

You must perform an inner join between the two tables to link them together into a bigger virtual row that you can then filter and select individual columns from.

The tutorial provides better examples than I can.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply