error while deleting from database

Question

I have 2 tables MachineGroups and Machines. Machine Groups has values

MachinegroupID
MachineGroupName
MAchineGroupDesc

And Machines has values

MachineGroupID (FK)
MachineID
MachineName Machinedesc

Now i want to delete am machinegroup but it gives me an error because there are already values in it.

So i want to delete those values which have no machines in it and give an error message if machines are preset in a particular machinegroup.

I tried workin with a query but it does not work..

System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection(); dataConnection.ConnectionString = @"Data Source=JAGMIT-PC\SQLEXPRESS;Initial Catalog=SumooHAgentDB;Integrated Security=True";

            System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
            dataCommand.Connection = dataConnection;
            long MachineGroupID = Convert.ToInt64(Request.QueryString["node"]); 
            //tell the compiler and database that we're using parameters (thus the @first, @last, @nick)  
            **dataCommand.CommandText = ("Delete from [MachineGroups] where  [MachineGroupID]=@MachineGroupID not in ( select distinct MachineGroupId  from Machines )");**

            //add our parameters to our command object  
            dataCommand.Parameters.AddWithValue("@MachineGroupName", MachineGroupName);
            dataCommand.Parameters.AddWithValue("@MachineGroupDesc", MachineGroupDesc);
            dataCommand.Parameters.AddWithValue("@TimeAdded", TimeAdded);
            dataCommand.Parameters.AddWithValue("@MachineGroupID", MachineGroupID);
            dataConnection.Open();

            dataCommand.ExecuteNonQuery();
            dataConnection.Close();

Here i am trying to delete a particular machineGroup... If there is another way to do it please suggest.

Thanks

Answer 1

I have not tried this part, but it should give you an idea

Delete from MachineGroups 
WHERE NOT EXISTS ( SELECT TOP 1 MachineID FROM Machines 
WHERE Machines.MachineGroupID= MachineGroups.MachineGroupID )

Answer 2

Delete from MachineGroups 
where MachineGroupId not in 
    (select distinct MachineGroupId  from Machines);