I posted the schema to the three tables I’m working with @ http://sqlfiddle.com/#!2/892ba
There are eight fields that are most important. Tables gs and gw_geog each have a field named IDArea, on which they’re joined. Tables gs and gs_planimals are joined on a field named Latin. The field gs.Symbol is a little redundant; it’s essentially the same as the field gs_planimals.Common.
Most symbolic plants and animals are species, represented by Rank = 65 in the table gs_planimals. However, gs_planimals also includes higher taxons, such as families and orders.
Most of the other fields are qualifiers used to select particular groups of plants or animals (e.g. mammals) or particular regions (e.g. North America).
So here’s my problem. Let’s say I want to display a list of symbols of the 50 U.S. states, and those symbols include the bison but not the common dolphin. I can join these three tables in such a way that every common and scientific (Latin) name in table gs_planimals displays, like this:
Cetacea (whales)
Didelphidae (dolphins)
Delphinus-delphinus (common dolphin)
Artiodactyla (hoofed mammals)
Bovidae (horned mammals)
Bison-bison (bison)
Or I can display only values that occur in tables gs_planimals and gs both. However, that would limit the display to species, like this:
bison (Bison bison)
I’d like to know if there’s a way to select and display bison PLUS its parent, grandparent, great grandparent, etc. So the display would look like this:
Artiodactyla (hoofed mammals)
Bovidae (horned mammals)
Bison-bison (bison)
(Note: I’m not actually displaying the parent or genus level. But I am displaying the grandparent, or family, and everything above it. But it’s fine to include all levels in the query, as I can easily filter out the levels I don’t want to display with a simple PHP switch.)
In this example, bison has the following values:
Table gs
IDArea | Symbol | Latin
us-mt | bison | Bison bison
us-wy | bison | Bison bison
Table gw_geog
IDArea | Name
us-mt | Montana
us-wy | Wyoming
Table gs_planimals
Latin | Common | Parent
Artiodactyla | hoofed mammals | Mammalia
Bovidae | horned mammals | Artiodactyla
Bison | bison | Bovidae
Bison-bison | American bison | Bison