Hello ehwagner,
I'm not completely sure I understood want you want to achieve, so here is what I think :
You have a MAIN table with data in it
You have a CHILD table with data in it AND reference to IDs in the main table
You want to SELECT some info in MAIN table, some related info in CHILD table and put them together in a third table we will call MIX table
I see two approches but :
beware of typos, I did not check this code, I just wrote it on the fly
this of course is usable only if I understood your question correctly
FIRST APPROACH :
If you query both tables at the same time, you can assign values to variables in one procedure and INSERT them in the MIX table in one go.
procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
var
ResultMain: TDataSet; //Best way (I know...)to save multiple query results in one variable
ResultChild : TDataSet; //Same thing but for the CHILD table
id, name, phone, email, salary, bonus : String; //All the variables from both queries to save in MIX table
begin
SQLQuery('SELECT id, name, phone, email FROM main', ResultMain);
begin
id := ResultMain.FieldByName('id').AsString;
name := ResultMain.FieldByName('name').AsString;
phone := ResultMain.FieldByName('phone').AsString;
email := ResultMain.FieldByName('email').AsString;
end;
SQLQuery('SELECT salary, bonus FROM child WHERE id="'+id+'"', ResultChild); //The id is the key to your MAIN table
begin
salary := ResultChild.FieldByName('salary').AsString;
bonus := ResultChild.FieldByName('bonus').AsString;
end;
SQLExecute('INSERT INTO mix(name, phone, email, salary, bonus) VALUES("'+name+'","'+phone+'","'+email+'","'+salary+'","'+bonus+'"');
//Free the DataSets at the end
ResultMain.Free;
ResultChild.Free;
end;
SECOND APPROACH :
You do not query the MAIN and CHILD tables at the same time, so you can not INSERT in the MIX table in one GO.
The approach is the same, except that you will save data in two steps.
The first query will be an INSERT in the MIX tableand will automaticly generate an ID
The second query will be an UPDATE on the same record in the MIX table based on the same ID.
All you need for the UPDATE query is to find the ID of the INSERT query.
If it was the last operation (I don't know how your application is structured), then you can use either last_Insert_id('mix') or find the MAX(id) on the MIX table and do your update on that record id.
If the record is updated long after (with potentialy other records beeing done before), then you'll have to find the corresponding id based on other criteria before you can update.
Hope it helps.
This is only my approach of course, let's wait to see if others do it differently.
Cheers
Mathias
I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor