The direct answer to your question is that all SQL statements (in all SQL implementations, not just SQL Server) are limited to modifying a single table. So an INSERT inserts rows into one table, an UPDATE updates rows in one table, a DELETE deletes rows from one table.
So you need to either cascade as suggested by another post, or you need to hold the row selectors for each table in a temporary table and then delete. The problem is that in order for the data modification to be transactional you can not just start deleting rows in a table without affecting other tables - in your exampleif the statement deleted a row in one table then the results of your SELECT are immediately different, so rows in the other tables would no longer be in the result-set and wouldn't be deleted anyway!
So, what you could do is something like this (I leave the details to you)
Run your SELECT with the INTO #temptable clause - you can remove any columns from the select list other than those used to identify rows in the tables you want to delete from. Then write three separate DELETEs that delete from the three tables joining to the #temptable to get the right rows. Do all of this inside a transaction (BEGIN TRAN ... COMMIT/ROLLBACK) so that you can rollback if there is a problem, and also to make sure that no-one else uses the rows once the deletes start.