CODEWITHSUNDEEP
mysqlsqlleft-join
Sunil Khiatani
Sunil Khiatani

Reputation: 337

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>

Upvotes: 0

Views: 59

Answers (2)

sjdaws
sjdaws

Reputation: 3536

Do your query around the other way:

SELECT r.*, p.*
FROM reviews AS r
LEFT JOIN products AS p
    ON p.id = r.productid
GROUP BY r.productid 
ORDER BY r.date DESC;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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
                       );

Upvotes: 2

Related Questions