LuckyLuke82 LuckyLuke82 - 1 month ago 10
Vb.net Question

SQL INSERT SELECT from Table2 to Table3

I'm trying to insert ID from Table1 and Table2 into Table3.My table design is:

Table1:
Id_Table1
field1 etc.

Table2:
Id_Table2
field1 etc.

Table3:
Id_Table3
Id_Table1_FK
Id_Table2_FK


I do this when I save records for Table1. Meanwhile I have Datagridview in my form, which uses comboboxcell to view records from Table2. When I select some record from Table2, both ID's (Table1 & Table2) needs to be inserted into Table3.

Here is my code so far (obviously wrong):

INSERT INTO Table3 (Id_Table3, Id_Table1, Id_Table2)

SELECT Id_Table2 FROM Table2 WHERE serial_no=" & MyDGV.CurrentRow.Cells(0).Value.ToString

VALUES (Id_Table3_seq.nextval, Id_Table1_seq.currval,????)


So, my problem stucks with Id_Table2 which needs to be selected first by Datagridview.Row cell value, and then result passed like a parameter to Table3. Any suggestions ? Probably an easy one, I just don't know how to start...

Answer

If I got your point right, this is what you need maybe?

INSERT INTO Table3 (Id_Table3, Id_Table1, Id_Table2) 
VALUES (Id_Table3_seq.nextval, Id_Table1_seq.currval,(
SELECT Top 1 Id_Table2 FROM Table2 WHERE serial_no='" & MyDGV.CurrentRow.Cells(0).Value.ToString & "'
))

Edited: Added Top 1 to make sure the subquery will only return 1 recordset. It's optional if the field you are selecting is unique.