this question has answer here:
- how full outer join in mysql? 12 answers
sorry long explanation, don't know how explain briefly... have table (table_animals) has scientific names of animals in field named taxon. rows in table (where n numerical key):
n | taxon | parent | commonname | rank 13 | ursus-maritimus | ursus | polar bear | 65 scientific names change or reorganized, i'm updating table accordingly. first created new table (table_animals_new) featuring current scientific names. has 2 fields, taxon , rank...
n | taxon | rank 9 | ursus-maritimus | 65 i want somehow combine 2 tables display in both tables.
so let's represents 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 data in 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 fictitious example, blue bear (ursus-blue) no longer recognized species, isn't listed in new table. however, scientists discovered new species - red bear (ursus-red), isn't listed in old table. also, bears placed in family ursinidae, rather ursidae.
what is...
1) display rows new table (table_animals_new)
2) display miscellaneous information old table on matching rows. if new table has row taxon = 'ursus', , old table has row taxon = 'ursus', fields old table associated ursus (e.g. commonname) displayed.
3) display "orphaned rows" - rows in old table don't match in new table (e.g. taxon = 'ursus-blue').
4) somehow mark orphaned rows "orphaned." if have table 50,000 rows, , see row taxon = 'red-blue', i'm not going have clue table comes from. column says "orphaned row" or "not orphaned" (or null).
one more option cool if not difficult...mark taxons appear in new table not old table "new."
so finished table might 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 can in 2 or 3 operations, figure out how 1 query. below more simplified example of i'm trying 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)
i suggest using union query. can break down 3 queries: - records exist in both - records exist in table - records exist in table b only
Comments
Post a Comment