Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014

database-designdeletesqlitesubquerytable

We are given 5 tables.

Orders

    Order number (Primary Key)

    Date of order

    Customer ID (Primary Key for Customers table)

    Serial Number (Primary Key for Machines table)

Customers

    ID (Primary Key)

    Name

    Phone Number

    Street (ie. 505 Ramapo Valley Road)

    City (ie. Mahwah)

    State

    Zip

Machines

    Serial Number (Primary Key)

    Model Number (Primary Key for Models table)

Model

    ID (Primary key)

    Speed

    RAM

    HD

    Price

    Manufacturer ID (Primary key for Manufacturer table)

Manufacturer

    ID (Primary Key)

    Name

    Phone Number

    Email Address

Our task is to create an SQL statement which will delete tuples from the Models table which haven't had a purchase since December 31st 2014.

The following is what I have managed to come up with thus far:

DELETE FROM Model

WHERE ID NOT IN

(

    SELECT Model.ID

    FROM Model

    INNER JOIN Machines

        ON Model.ID = Machines.ModelNo

    INNER JOIN Orders

        ON Machines.SerialNo = Orders.SerialNo

    WHERE OrderDate > 2015 

    GROUP BY Model.ID

);

Please let me know how I can fix this SQL statement, I believe the problem is either in the first or second line or possibly both.

Best Answer

Your main issue is you're using an INNER JOIN between Machines and Orders which means only return the rows that match (in other words, only the rows where Orders exist). Rather you should use an OUTER JOIN, in this case a LEFT OUTER JOIN (which can simply be written as LEFT JOIN) so you get all the rows of Models and Machines, and the ones that don't have Orders will show a NULL value for the columns in the Orders table. Then you can use the WHERE clause to filter out anything but the rows without Orders by saying WHERE Orders.SerialNo IS NULL like so:

DELETE FROM Model
WHERE ID IN
(
    SELECT Model.ID
    FROM Model
    INNER JOIN Machines
        ON Model.ID = Machines.ModelNo
    LEFT JOIN Orders
        ON Machines.SerialNo = Orders.SerialNo
        AND Orders.OrderDate >= '2015-01-01' 
    WHERE Orders.SerialNo IS NULL
    GROUP BY Model.ID
);

The other issue you had was your OrderDate field is likely storing more than just the year (I'm assuming), so you need to specify the whole date (not just the year) when you do a direct comparison. I fixed that in the above query as well.

Please read the resource I linked above on LEFT JOINS along with the other resources I provided you on your last post, specifically understanding the different join types and when to use them are very important concepts.

Related Question