Sorry for the LONG explanation, but I don't know how to explain this briefly... I have a table (table_animals) that has scientific names of animals in a field named Taxon. The rows in the table look something like this (where N is just a numerical key):
N | Taxon | Parent | CommonName | Rank
13 | Ursus-maritimus | Ursus | polar bear | 65
Scientific names sometimes change or are reorganized, so I'm updating my table accordingly. I first created a new table (table_animals_new) featuring current scientific names. It has just two fields, Taxon and Rank...
N | Taxon | Rank
9 | Ursus-maritimus | 65
I now want to somehow combine the two tables to display EVERYTHING in both tables.
So let's say this represents the old data (table_animals)...
N | Taxon | Parent | CommonName | Rank
11 | Ursidae | Carnivora | bear family | 45
12 | Ursus | Ursidae | typical bears | 55
13 | Ursus-maritimus | Ursus | polar bear | 65
14 | Ursus-blue | Ursus | blue bear | 65
Compare it to the data in the new table (table_animals_new)...
N | Taxon | Parent | CommonName | Rank
8 | Ursinidae | Carnivora | bear family | 45
9 | Ursus | Ursinidae | typical bears | 55
10 | Ursus-maritimus | Ursus | polar bear | 65
11 | Ursus-red | Ursus | red bear | 65
In this fictitious example, the blue bear (Ursus-blue) is no longer recognized as a species, so it isn't listed in the new table. However, scientists discovered a new species - the red bear (Ursus-red), which isn't listed in the old table. Also, bears are now placed in the family Ursinidae, rather than Ursidae.
What I would like to do is...
1) Display all the rows from the new table (table_animals_new)
2) Display all the miscellaneous information from the old table on matching rows. So if the new table has a row where Taxon = 'Ursus', and the old table also has a row where Taxon = 'Ursus', then fields from the old table that are associated with Ursus (e.g. CommonName) would also be displayed.
3) Display "orphaned rows" - rows in the old table that don't match anything in the new table (e.g. Taxon = 'Ursus-blue').
4) Somehow mark the orphaned rows as "orphaned." If I have a table with 50,000 rows, and I see a row where Taxon = 'red-blue', I'm not going to have a clue which table it comes from. So I would like an extra column that says "orphaned row" or "not orphaned" (or NULL).
One more option that would be really cool if not too difficult...mark Taxons that appear in the new table but not the old table "NEW."
So the finished table might look something like this:
N | Taxon | Parent | CommonName | Rank | Orphaned
11 | Ursidae | Carnivora | bear family | 45 | orphan
12 | Ursinidae | Carnivora | bear family | 45 | NEW
13 | Ursus | Ursidae | typical bears | 55 | (NULL)
14 | Ursus-maritimus | Ursus | polar bear | 65 | (NULL)
15 | Ursus-blue | Ursus | blue bear | 65 | orphan
16 | Ursus-red | Ursus | red bear | 65 | NEW
I think I can do this in two or three operations, but I would like to figure out how to do it with just one query. Below is an even more simplified example of what I'm trying to do.
Old Table
A
B
C
D
New Table
A
B
D
E
Merged Display
A
B
C (orphan - in old table only)
D
E (New - in new table only)
