SQL : Deleting records found using 3 joins-CodePudding

I am using SQL Server 2014 database, and SQL Server Management Studio to create and run queries.

Tables are :

Persons

| ID | personName |
 ---- ------------ 
| 1  |    Hamish  |
| 2  |    Morag   |
| 3  |    Ewan    |

Cars

| ID | CarName |
 ---- --------- 
| 1  |  Humber |
| 2  |  Austen |
| 3  |  Morris |

Gadgets

| ID | GadgetName |
 ---- ------------ 
| 1  |  Cassette  |
| 2  |     CD     |
| 3  |   Radio    |

CarToPersonMap

| ID | CarID | PersonID |
 ---- ------- ---------- 
| 1  |   1   |    1     |

CarToGadgetMap

| ID | CarID | GadgetID |
 ---- ------- ---------- 
| 1  |   2   |    2     |

The map tables have the appropriate foreign keys.

I want to delete records where a Car exists but is unused. So in the example above I want to delete Car with ID = 3.

I have a SELECT statement that uses 3 JOINs as follows. The statement works and it returns the correct row(s), i.e. Car with ID = 3.

SELECT * 
FROM
    (SELECT Cars.*
     FROM Cars 
     LEFT JOIN CarToGadgetMap ON Cars.ID = CarToGadgetMap.CarID
     WHERE CarToGadgetMap.CarID IS NULL) t1
JOIN
    (SELECT Cars.*
     FROM Cars 
     LEFT JOIN PersonToCarMap ON Cars.ID = PersonToCarMap.CarID
     WHERE PersonToCarMap.CarID IS NULL) t2 ON t1.ID = t2.ID

When I attempt a DELETE using the code below, it deletes all 3 Car rows:

DELETE Cars 
FROM
    (SELECT Cars.*
     FROM Cars 
     LEFT JOIN CarToGadgetMap ON Cars.ID = CarToGadgetMap.CarID
     WHERE CarToGadgetMap.CarID IS NULL) t1
JOIN
    (SELECT Cars.*
     FROM Cars 
     LEFT JOIN PersonToCarMap ON Cars.ID = PersonToCarMap.CarID
     WHERE PersonToCarMap.CarID IS NULL) t2 ON t1.ID = t2.ID

Result message:

(3 row(s) affected)

and inspection shows all 3 rows in the Cars table have been deleted.

Why do all records get deleted, when the SELECT statement returns just 1 row?

Can anyone help, please?

Thanks in advance

CodePudding user response：

You can do it by just using this code. I tested it by creating a database & data at my end.

 DELETE Cars
 FROM Cars 
 LEFT JOIN CarToPersonMap ON Cars.ID = CarToPersonMap.CarID
 LEFT JOIN CarToGadgetMap ON Cars.ID = CarToGadgetMap.CarID
 WHERE CarToPersonMap.CarID IS NULL and  CarToGadgetMap.CarID IS NULL

CodePudding user response：

Maybe use a WHERE clause instead of FROM.

DELETE cars WHERE cars.id = 3 (Get your query select only the id from your logic, rather than selecting all the tables with cars.*)