On Oct 5, 2007, at 4:11 , Stefan Schwarzer wrote:
Alternately, you could have a gdp table and a fish_catch table which
would be easily joined to give the same result.
Expanding on this:
create table fish_catches (country text not null,
data_year date not null,
primary key (country, data_year),
fish_catch numeric not null);
create table gdp (country text not null reference countries
data_year date not null,
primary key (country, data_year),
gdp numeric not null);
This makes your queries quite simple:
select country, data_year, fish_catch, gdp
from fish_catches
natural join gdp
where country = :country
order by data_year
limit 1;
Hmmm..... Don't really get that query working. My SQL looks like
this now:
SELECT
id_country,
year,
value
FROM
internet_users
NATURAL JOIN
gdp
WHERE
id_country = 8
ORDER BY
year
LIMIT
1
But there is no result.
My table looks like this (for each variable one table):
id_country year value
The natural join operator joins on common columns: if columns are
named "value" in both tables, the join condition is (id_country,
year, value) = (id_country, year, value). In the example I provided
above, the tables were fish_catches {country, year, fish_catch} and
gdp {country, year, gdp}: the join condition is (country, year) =
(country, year).
Also, note that there are *four* output columns in the query I used:
{country, data_year, fish_catch, gdp}. You've only got three, which
is bound to be confusing.
In your case you can use subqueries to rename the columns or an
explicit join:
-- using subqueries
select id_country, year, internet_users, gdp
from (select id_country, year, value as internet users
from internet_users) i
natural join (select id_country, year, value as gdp
from gdp) g
where id_country = 8
order by year
limit 1
-- using an explicit join
select id_country, year, internet_users.value as internet_users,
gdp.value as gdp
from internet_users
join gdp using (id_country, year)
where id_country = 8
order by year
limit 1
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
