CODEWITHSUNDEEP
sqlsql-delete
user2535309
user2535309

Reputation: 15

Delete all records unless 1 or more sub record meet a criteria

If I have a table "Cars" with a 1-to-many joined table "Passengers" how can I delete all cars that don't have one or more passengers named "Jim"?

If it makes it simpler, their is a static amount of other passenger names. Let's say only "Peggy", "Maggy", and "Sue". Doesn't sound like it would be that hard but I can't figure it out for the life of me. Thanks in advance!

Upvotes: 1

Views: 112

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460238

One way, NOT EXISTS:

DELETE c FROM Cars c
WHERE NOT EXISTS
(
    SELECT 1 FROM Passengers p
    WHERE p.Name = 'Jim'
      AND p.CarID = c.ID
)

Demo

Presumed that CarID is the foreign-key and ID is the primary key in Cars.

Upvotes: 2

Related Questions