CODEWITHSUNDEEP
mysqlsubqueryleft-join
coffeemonitor
coffeemonitor

Reputation: 13120

mysql subquery inside a LEFT JOIN

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.

Upvotes: 22

Views: 96536

Answers (2)

mrmryb
mrmryb

Reputation: 1509

Would this not work?

SELECT t1.datesent,t1.customerid,t2.email,t2.name
FROM
(SELECT max(datesent) AS datesent,customerid
FROM `tbl_emails_sent`
) as t1
INNER JOIN `tbl_customers` as t2
ON t1.customerid=t2.customerid

Only issue you have then is what if two datesents are the same, what is the deciding factor in which one gets picked?

Upvotes: 5

John Woo
John Woo

Reputation: 263733

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

Upvotes: 43

Related Questions