I have a list of about 30,000 animal species matched to their native
ecoregions in a table named gzecoregions. Another table contains the
list of all the ecoregions AND their parents (biomes) and THEIR parents
(realms) in a parent-child relationship.
Thus, I can use the following query to display a list of ecoregions the
wolf is native to, where $Animal = 'wolf':
Quote:
$area_res = mysql_query("SELECT DISTINCT GWER.ERName FROM gwecoregions
as GWER
|
INNER JOIN gzecoregions GZER ON GZER.Place = GWER.ERID
WHERE GZER.Animal = '$Animal'");
Alaska Tundra
Canadian Taiga
Siberian Taiga
The problem is I can only display ecoregions, since those are the only
areas listed in table gzecoregions. But I would also like to also
display the ecoregions' parents (biomes) and the biomes' parents
(realms). I discovered that I can't do it using just one table, so I
created two new tables for biomes and realms. But I can't figure out how
to join them so that I can display something like this:
NEARCTIC (realm)
TUNDRA (biome)
Alaska Tundra
BOREAL FOREST (biome)
Canadian Taiga
PALEARCTIC (realm)
BOREAL FOREST (biome)
Siberian Taiga
It should be easy, but I'm doing something wrong. The following diagrams
illustrate the relevant tables and fields...
Quote:
TABLE: gwecorealms
RealmID | Realm | RealmParent
|
R-NA | Nearctic | na
R-NT | Neotropical | na
R-NT | Neotropical | sa
R-PA | Paleotropical | eur
R-PA | Paleotropical | afr
TABLE: gwecobiomes
BiomeID | Biome | BiomeParent
B11-NA | Tundra | R-NA
B11-PA | Tundra | R-PA
B11-AA | Tundra | R-AA
B11-AN | Tundra | R-AN
B12-NA | Desert | R-NA
TABLE: gwecoregions
ERID | ERName | ERParent
R-NA | Nearctic | na
R-NT | Neotropical | na
B11-NA | Tundra | R-NA
B11-PA | Tundra | R-PA
B11-AA | Tundra | R-AA
NA1424 | Alaska Tundra | B11-NA
PA1340 | Siberian Tundra | B11-PA |
Note that EVERY area, including all ecoregions, biomes and realms, is
included in table gwecoregions. So I display my data via field ERName.
Gwecoregions also has a field named ERParent, which includes the ID of
each region's parent, so everything is listed in a parent-child
relationship.
Below is a copy of my original query, followed by one query I used to
join all four tables. Both queries work, but they produce the same
results - a list of ecoregions only. How can I join them so my script
also displays the parents?
Thanks.
Quote:
$area_res = mysql_query("SELECT DISTINCT GWER.ERName FROM gwecoregions
as GWER
|
INNER JOIN gzecoregions GZER ON GZER.Place = GWER.ERID
WHERE GZER.Animal = '$Animal'");
$area_res = mysql_query("SELECT * FROM gwecoregions GWER
INNER JOIN gzecoregions GZER ON GZER.Place = GWER.ERID
INNER JOIN gwecobiomes GWEB ON GWEB.BiomeID = GWER.ERParent
INNER JOIN gwecorealms GWERM ON GWERM.RealmID = GWEB.BiomeParent
WHERE GZER.Animal = '$Animal' GROUP BY RealmN, BiomeN, ERN");