delete from coRoomToGroup where RoomID IN ( select roomid from (.... your query ....)>The query below returns me a listing of "inactive" records that I want to remove from a table. I'm wanting to create this as a stored procedure that will delete all records in the coRoomToGroup where the RoomID is contained in this query result. I'm not sure how to structure the delete part of that query. Thanks for any help.
>select * from >( > select *, > (Select deptDesc from coDept where coDept.DeptID=det2.RoomDeptID) as DeptDesc, > (Select isactive from coDept where coDept.DeptID=det2.RoomDeptID) as DeptIsActive > from > ( > select det.RoomID,det.RoomName,det.RoomIsActive,det.RoomDeptID > from > ( > select *,(Select isactive from dbo.coRooms where coRooms.roomID=coRoomToGroup.RoomID) as RoomIsActive, > (Select deptID from dbo.coRooms where coRooms.roomID=coRoomToGroup.RoomID) as RoomDeptID, > (Select RoomName from dbo.coRooms where coRooms.roomID=coRoomToGroup.RoomID) as RoomName > from coRoomToGroup) det > )det2 >)det3 >where det3.DeptIsActive=0 or det3.RoomIsActive=0 >