How do we use distinct in left join of mysql

Question

I am using PHP and MySQL for my application.

I have a table "Enquiries" with PK enquiry_id and a table "Enquiry_comments" with FK enquiry_id. There will be multiple comments for each enquiry. In my join how do I show the last comment only against each enquiry?

SELECT * FROM enquiries AS e LEFT JOIN enquiry_comments AS ec ON ec.enquiry_id = e.enquiry_id WHERE e.status = 'Active' ORDER BY e.enquiry_id DESC LIMIT 10

The above query gives me duplicate records so I want to use group by or distinct on enquiry_id and order by enquiry_comment_id DESC for enquiry_comments and show only the recent comment with the listing.

The Table Structure:

Table Enquiries

enquiry_id  enquiry_about enquiry_date
1           PHP           2017-09-20
2           MySQL         2017-08-21

Table Enquiry_comments

enquiry_comment_id enquiry_id enquiry_comment enquiry_comment_date
1                     1       PHP is Easy     2017-08-03
2                     1       PHP is Ver 7    2017-09-17
3                     2       MySQK is RDBS   2017-09-13

From the above example data I want to get the latest one comment with each enquiry so PHP should get one comment with enquiry_comment_id 2 which is latest and MySQL get the enquiry_comment_id 3

The Listing Data I want

1   PHP    PHP is Ver 7    2017-09-17
2   MySQL  MySQK is RDBS   2017-09-13

Answer 1

Try this query:

SELECT
    t1.enquiry_id,
    t1.enquiry_about,
    (SELECT t2.enquiry_comment FROM Enquiry_comments t2
     WHERE t2.enquiry_id = t1.enquiry_id
     ORDER BY t2.enquiry_comment_date DESC LIMIT 1) AS enquiry_comment,
    (SELECT MAX(t2.enquiry_comment_date) FROM Enquiry_comments t2
     WHERE t2.enquiry_id = t1.enquiry_id) AS enquiry_comment_date
FROM Enquiries t1
ORDER BY t1.enquiry_id;

For each record in the Enquiries table, this query does a subquery on Enquiry_comments and selects the latest comment for each parent enquiry. To find the latest record it uses an ORDER BY with LIMIT trick. To also find the corresponding latest comment date, we can just use a simple subquery with the MAX() function.

Output:

Demo here:

Rextester