Sunil Khiatani Sunil Khiatani -4 years ago 49
MySQL Question

Get Unique/Distinct results for SQL Query on JOIN

I have 2 tables: products and reviews, where each product has many reviews:

table products has the following columns: id, name
table reviews has the following columns: id, productid, created_on, rating, review. Where the productid is a foreign key, and created_on is type datetime.

Sample data as follows:



<table>
<tr>
<th>product id</th>
<th>product name</th>
</tr>
<tr>
<td>1</td>
<td>Foo</td>
</tr>
<tr>
<td>2</td>
<td>Bar</td>
</tr>
</table>

<table>
<tr>
<th>review id</th>
<th>product id</th>
<th>rating</th>
<th>review</th>
<th>created_on</th>
</tr>
<tr>
<td>1</td>
<td>1</td>
<td>5</td>
<td>Perfect foo</td>
<td>2017-1-1Z00:00:00</td>
</tr>
<tr>
<td>2</td>
<td>1</td>
<td>1</td>
<td>This foo is not the foo I was looking for</td>
<td>2017-2-2Z00:00:00</td>
</tr>
<tr>
<td>3</td>
<td>1</td>
<td>4</td>
<td>Foo-tastic</td>
<td>2017-3-3Z00:00:00</td>
</tr>
<tr>
<td>4</td>
<td>2</td>
<td>4</td>
<td>Bar is Bar/10</td>
<td>2017-3-3Z00:00:00</td>
</tr>
<tr>
<td>4</td>
<td>2</td>
<td>5</td>
<td>Barmendous!!!</td>
<td>2017-1-1Z00:00:00</td>
</tr>
</table>





I want to be able to get the latest review for each product but I'm unsure how to do it. It should be something like:

SELECT products.product_name, reviews.rating, reviews.review FROM products LEFT JOIN products ON products.id = reviews.productid ORDER BY reviews.created_on DESC;


But this will return multiple results for each product. I only need one review for each product, preferably the most recent review.

MySQL 5.x or above is the preferred database in this case.

Sample out is as follows:



<table>
<tr>
<th>product name</th>
<th>rating</th>
<th>review</th>
</tr>
<tr>
<td>Foo</td>
<td>4</td>
<td>Footastic</td>
</tr>
<tr>
<td>Bar</td>
<td>4</td>
<td>Bar is Bar/10</td>
</tr>
<table>




Answer Source

If you want the latest review for each product, then use a WHERE clause:

SELECT p.product_name, r.*
FROM products p LEFT JOIN 
     reviews r
     ON p.id = r.productid AND
        r.created_on = (SELECT MAX(r2.created_on)
                        FROM reviews r2
                        WHERE r2.productid = r.productid
                       );
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download