David Blomstrom wrote:
> I have a PHP script that displays data like this:
> Eurasia
> Eurasia<sup>island</sup>
> Africa
>
> Where Eurasia and Africa are mainland parents of
> ecological regions and Eurasia<sup>island</sup> is a
> parent of an ecological system that is associated with
> a continent. For example, Borneo would be
> Eurasia<sup>island</sup>.
You've lost me here ;-) I know quite a bit about animals, but ecological
regions and their parents are not my cup of tea.
> ANIMALS TABLE
> Canis_lupus | wolf
> Panthera_tigris | tiger
>
> JOIN TABLE
> SPECIES | ECOREGION
> Canis_lupus | NA1008
> Canis_lupus | NA1010
>
> ECOREGIONS TABLE
> ID | NAME | Geog | Geog2
> NA1008 | Alaska tundra | na | na
> IM1003 | Philippine rainforest | eur | phl
> (Note that mainland ecoregions feature the continental
> ID in each of the last two columns, while island
> ecoregions feature the island's ID in the last
> column.)
>
> GEOGRAPHY TABLE
> ID | NAME
> na | North America
> phl | Philippines
I miss quite a bit of data here and there are no queries mentioned.
Where can I find Eurasia and Africa here?
Maybe you can start by rewriting the problem is pseudo queries:
"I want a list of the NAMEs from the GEOGRAPHY table for a certain
species from the JOIN table for which the ecoregions and the geog are
listed in the ecoregions table. Each NAME should only appear once."
Or something like that.
With such a natural language pseudo query you're often more than half
way towards building a real query. At least you're far enough for others
to help you build the actual SQL.
Regards, Jigal.
