1 I think it may help to understand that the idea of relational dbs and SQL, is that it is a set-processing paradigm, which is also called client-server: the client sends a batch of SQL (request) to the server; which processes the SQL; and returns a result set. What you are doing in post #4 is a record-processing paradigm, for which SQL was not designed, for which you have to turn the set-processing engine upside-down (different suppliers provide different degrees of capability, but it is "not SQL"). Therefore you may want to look at a application coding language (or objects/classes) that have result set-processing capability, that is they have objects/windows that handle a whole result set which is received from the server.
2 In the set-processing world:
Code:
select AttributeId, Checked="Y" from ProductAttribute where ProductId = @ProductID
union all -- product specific
select AttributeId, Checked="N" from Attribute where AttributeId not in (select AttributeId, Checked="Y" from ProductAttribute where ProductId = @ProductID)
--
-- this will give you a result set, of a full set of possible Attributes for the given Product, with Checked = {Y|N}, which you will load into memory (object, DataWindow, whatever)
-- display the set (“form” that allows changes to Attribute for a Product)
-- allow the user Un/Check the box/column of the set/form in memory
-- track the changes to columns in memory (if FieldChanged ...)
-- upon SaveClicked, for each changed row in the set/form in memory:
-- if Checked then insert ProductAttribute ...
-- if Unchecked then delete ProductAttribute ...
If you do not have FieldChanged capability in your form/set object, simply save a copy “Original” of the object before you display it, and compare the changed set against the original, to determine which rows/fields have changed.
For the last two lines in the code above, I have used simple SQL pseudo-code for illustration. You should expand the two lines into a proper transaction with error checking.