The below query returns me two rows:
SELECT
DocList.DocListId, RegistrationDocList.RegistrationDocListId, Registration.RegistrationId
FROM
DocList INNER JOIN
RegistrationDocList ON DocList.DocListId = RegistrationDocList.DocListId INNER JOIN
Registration ON RegistrationDocList.RegistrationId = Registration.RegistrationId
WHERE (DocList.DocListId = 547)
Results:
--------------------------------------------------------
DocListId RegistrationDocListId RegistrationId
-------------------------------------------------------
547 097 115
547 098 116
UPDATE:
as you can see its using three tables, so i want to delete the rows from:
DocList where doclistid = 547
RegistrationDocList.RegistrationId where RegistrationId in (097,098)
Registration.RegistrationId where RegistrationId in (115,116)
You need to cache the values to delete on the far side of the link table, delete the link records, and then delete the linked records:
DECLARE @reg_to_delete TABLE (id int)
INSERT INTO @reg_to_delete (id)
SELECT RegistrationID FROM RegistrationDocList WHERE DocListID = 547
DELETE FROM RegistrationDocList WHERE DocListID = 547
DELETE FROM RegistrationList WHERE RegistrationID IN (SELECT id FROM @reg_to_delete)
DELETE FROM DocList WHERE DocListID = 547
Maybe I missed your point. I hope this code will help you:
DECLARE @Result int
BEGIN TRAN
BEGIN TRY
DELETE DocList where doclistid = 547
DELETE RegistrationDocListwhere RegistrationId in (097,098)
DELETE Registration where RegistrationId in (115,116)
/* code was done properly => mark flag as OK */
SET @Result = 0
END TRY
BEGIN CATCH
/* some error occured => mark flag as ERROR */
SET @Result = -1
END CATCH
IF @Result = 0 BEGIN
/* all is fine, transaction can be commited */
COMMIT TRAN
END
ELSE BEGIN
/* something is wrong, transaction must be rolled back */
ROLLBACK TRAN
END
