Query not working properly with LEFT JOIN (MySQL)

Question

I'm still struggling with this problem (my previous post was about that, and since I've changed the table parameters, I've got another problem):

(Some columns have changed, so it's a different problem/question, since I only have the id as field to get the last insert)

table products:

id  name
1   TV
2   RADIO
3   COMPUTER

table sales (product_id is a FK which refers to products):

id_sales    feedback    product_id
4           GOOD        2
5           GOOD        3
6           NICE        3

The query I'm using:

SELECT products.name, sl.feed 
FROM products LEFT JOIN (
SELECT product_id, max(id_sales), feedback AS feed FROM sales GROUP BY product_id) sl
ON products.id = sl.product_id

The result is:

name         feed   
TV           NULL
RADIO        GOOD
COMPUTER     GOOD

It worked for TV and Radio, but in column Computer, I must show the LAST feedback, which you can see it's 'NICE', not 'GOOD'! Using SQL function MAX(id_sales) somehow is not the solution I thought it was. What am I missing here?

Possible duplicate of [Returning a Min() and another field?](https://stackoverflow.com/questions/52348182/returning-a-min-and-another-field) — Guy Louzon, Sep 26 '18 at 17:34
possible duplicate of https://stackoverflow.com/questions/52348182/returning-a-min-and-another-field/52348344#52348344 — Guy Louzon, Sep 26 '18 at 17:35
notice the duplicate question. It suggests to use join with an inequality, to get the max value and the relevant line — Guy Louzon, Sep 26 '18 at 17:36

score 1 · Answer 1 · answered Sep 26 '18 at 17:49

The best method is row_number(), but that is probably not available. So, you can filter for the most recent date in the ON clause:

SELECT p.name, sl.feed 
FROM products p LEFT JOIN
     sales s
     ON p.id = s.product_id AND
        s.id_sales = (SELECT MAX(s2.id_sales) FROM sales s2 WHERE s2.product_id = s.product_id);

There are alternative methods for expressing this logic. One big advantage of this approach is that it can make maximal use of indexes for performance.

score 0 · Answer 2 · answered Sep 26 '18 at 17:35

0

SELECT products.name, sl.feed FROM products LEFT JOIN ( SELECT product_id, rank() over (partition by product_id Order by id_Sales desc) pRank, feedback AS feed FROM sales ) sl ON products.id = sl.product_id where sl.pRank = 1

Does this work for you?

answered Sep 26 '18 at 17:35

Scotch

3,186
11
35
50

Some words weren't recognized in MySQL. Thanks anyway! – Sep 26 '18 at 20:21
Ah I don't think it was tagged as my sql. I don't think they support the partitioning functions the other solution provided should work – Scotch Sep 26 '18 at 20:26

score 0 · Accepted Answer · answered Sep 26 '18 at 17:45

0

use sub-query and left join

select p.name,t1.feedback from products p
  left join
    (
    select t.id_sales,t.product_id,s.feedback 
       from
    (
    select max(id_sales) as id_sales,product_id from sales  
    group by product_id 
    ) t join sales s on  t.id_sales=s.id_sales
    ) t1 on p.id=t1.product_id

answered Sep 26 '18 at 17:45

Zaynul Abadin Tuhin

31,407
5
33
63

That worked. Thanks! I'm trying to understand the query right now. – Sep 26 '18 at 20:18

Query not working properly with LEFT JOIN (MySQL)

3 Answers3