SELECT DISTINCT
( CASE WHEN d.year=2003 AND pt.year=2003 AND pt.value <> '0' AND
pt.value IS NOT NULL THEN d.value / pt.value ELSE NULL END ) AS
y_2003,
( CASE WHEN d.year=2002 AND pt.year=2002 AND pt.value <> '0' AND
pt.value IS NOT NULL THEN d.value / pt.value ELSE NULL END ) AS
y_2002,
c.name
FROM
public_multiple_tables.agri_area AS d
LEFT JOIN
public_multiple_tables.pop_total AS pt ON pt.id_country =
d.id_country
LEFT JOIN
countries_view AS c ON c.id = d.id_country
ORDER BY
name ASC
What am I doing wrong? Thanks for any advice,
You are trying to do the join on the year in the SELECT expression.
Also, you are trying to do the formatting into year-columns in your
query. You are left joining to tables in which there should always
be a
mathing row (I assume).
This should give you the same data out in a different format. Note
that
most of the NULL values will be excluded from this result.
SELECT cname, year, d.value/pt.value
FROM
public_multiple_tables.agri_area AS d
INNER JOIN
public_multiple_tables.pop_total AS pt ON pt.id_country =
d.id_country AND pt.year = d.year
INNER JOIN
countries_view AS c ON c.id = d.id_country
WHERE d.year in (2002,2003,2004)
AND pt.value <> 0
ORDER by c.name, year;
Hmmm.... Actually, my intention was to get a more "excel" like
output, that is the formatting into year-columns. This eases a lot
the PHP/HTML display/loop. Otherwise I would have to start to do some
joggling inside PHP to get it that way....
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
