Optimize a Query over Several Tables with Default

Question

I am trying to optimize some horribly slow queries on an old osCommerce site using the Recover Cart Sales contribution.

I am trying to pull information from the carts and customers tables (which I can do easily). The trick is that I need to figure out when they were last contacted (without having to do another query). That exists in another table, and if we have never contacted them before, there is no enty in the table, and so it should be null.

Here is what I have, and it works EXCEPT, that it only pulls data if there is an entry in the scart table.

SELECT DISTINCT cb.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address, sc.datemodified AS last_contacted
    FROM customers_basket cb, customers c, scart sc
        WHERE c.customers_id = cb.customers_id
            AND cb.customers_id = sc.customers_id 
            AND cb.customers_basket_date_added < 20130916 
            AND cb.customers_basket_date_added > 20130101
        AND cb.customers_id NOT IN(SELECT sc.customers_id  //Excludes people we've contacted in the last month
                                FROM scart sc
                        WHERE sc.datemodified >20130816)
    ORDER BY cb.customers_id DESC

Answer 1

It sounds like you need a LEFT JOIN? It will return the row you're interested in if it exists and a NULL if it doesn't.

SELECT DISTINCT cb.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address, sc.datemodified AS last_contacted
FROM customers_basket cb 
INNER JOIN customers c ON c.customers_id = cb.customers_id
LEFT JOIN scart sc ON cb.customers_id = sc.customers_id
WHERE cb.customers_basket_date_added < 20130916 
  AND cb.customers_basket_date_added > 20130101
  AND cb.customers_id NOT IN(SELECT sc.customers_id  //Excludes people we've contacted in the last month
                            FROM scart sc
                    WHERE sc.datemodified >20130816)
ORDER BY cb.customers_id DESC