mysql subquery inside a LEFT JOIN

mysql subquery left-join
coffeemonitor picture coffeemonitor · Sep 7, 2012 · Viewed 70.9k times · Source

I have a query that needs the most recent record from a secondary table called tbl_emails_sent.

That table holds all the emails sent to clients. And most clients have several to hundreds of emails recorded. I want to pull a query that displays the most recent.

Example:

SELECT c.name, c.email, e.datesent
FROM `tbl_customers` c
LEFT JOIN `tbl_emails_sent` e ON c.customerid = e.customerid

I'm guessing a LEFT JOIN with a subquery would be used, but I don't delve into subqueries much. Am I going the right direction?

Currently the query above isn't optimized for specifying the most recent record in the table, so I need a little assistance.

Answer

John Woo picture John Woo · Sep 7, 2012

It should be like this, you need to have a separate query to get the maximum date (or the latest date) that the email was sent.

SELECT  a.*, b.*
FROM    tbl_customers a
            INNER JOIN tbl_emails_sent b
                ON a.customerid = b.customerid
            INNER JOIN
            (
                SELECT      customerid, MAX(datesent) maxSent
                FROM        tbl_emails_sent
                GROUP BY    customerid
            ) c ON  c.customerid = b.customerid AND
                    c.maxSent = b.datesent

Related questions

How to make a "distinct" join with MySQL

I have two MySQL tables (product and price history) that I would like to join: Product table: Id = int Name = varchar Manufacturer = varchar UPC = varchar Date_added = datetime Price_h table: Id = int Product_id = int Price = int Date = datetime …

Read More
How to limit results of a LEFT JOIN

Take the case of two tables: tbl_product and tbl_transaction. tbl_product lists product details including names and ids while tbl_transaction lists transactions involving the products and includes dates, product-ids, customers etc. I need to display a web-page …

Read More
MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More