Dependent Insert

[Gavin Greenwalt]

Ok I'm way in over my SQL syntax head and need a wee bit of help.

I have 3 tables like the following:

Table1 with columns (T1GUID, GenusName)
Table2 with columns (T2GUID, T1GUID (FK), FamilyName)
Table3 with columsn (T3GUID, T2GUID (FK), SpeciesName)

I have a bunch of Species (2700), Quite a few Families (2000--pretty much one species per family) and quite a few Genuses (say maybe 1000).

Now I want to insert a few thousand more grandchildren which know the GenusName and FamilyName they belong to without creating duplicate Genus and Family entries? Worse still a significant majority of Family names are not unique. Let's just say hypothetically you have an entry for:

"RC Airplane" (Species) which it knows is a member of "Vehicles" (Family) and the (Genus) "Objects"

If there is no "Objects" I want to create it if not I want to retrieve its GUID and then either way feed its GUID into the FK column of... "Vehicles" if there is no entry "Vehicles" I want to create it if it does exist get its GUID and feed it finally in the FK slot of "RC Airplane"


I have a huge LEFT JOIN query command which brings all the data back together for a given species (working backwards up the foodchain) but I don't know how to do the opposite--Insert the data back into the schema. I would like to do it in a single SQL command if at all possible.

I found the INSERT IGNORE which works great for the Genus (set a unique key on the GenusName and the problem is solved). The real problem is the Family which can't be a unique name and is dependent on a Genus which just may or may not have been created (I'm assuming the last key updated function doesn't work if IGNORE is called)