Re: [GENERAL] Subqueries or Joins? Problems with multiple table query

[Stefan Schwarzer]

Select countries.name, basel.year, basel.value, cites.year, cites.value

From countries
Left Join basel on basel.id_country = countries.id_country and
basel.value=1
Left Join cites on cites.id_country = countries.id_country and
cites.value=1

I would have thought so, but the query turns forever.

How many rows in each of your tables, and what indexes do you have?

around 5000 rows

You may also wish to review Andreas' suggestions as they propose a
more sensible table structure rather than having a table for each
convention.

The table proposal really looks nice. But our database is structured by variable - so each convention has its own table.

You may also be interested in the tablefunc contrib module to help
present your data in the format you require rather than having a join
for each convention:

Yes, I use this for the online representation of the data. Very useful indeed.

smime.p7s
Description: S/MIME cryptographic signature