Selecting only last record inside the JOIN statement

Question

I have the following query:

    SELECT 
      usp.user_id AS userId,
      usp.create_time AS create_time,
      ml.amount AS amount      
    FROM user_subscription_plan AS usp
            RIGHT JOIN product AS product ON product.id = usp.product_id            
            LEFT JOIN modification_log AS ml ON ml.subscription_id = usp.id         
    WHERE usp.id IN ('447482')

I have three tables, from which I need to select data.

My problem begins with the last LEFT join.

modification_log table could have no entries, but also it could have more entries. I want to select only the latest entry. With the above query, if I have 2 (or more) entries in the modification_log, I receive 2 identical results (repeated).

What I would like to get:

If there are no results in the modification_log, then it will return null. I think that is covered with LEFT JOIN. But also, in the case of many record, I would need to select the latest added one (amount)

I believe I might need a sub-query, but I fail to implement it.

ok, so what is meant by `latest entry` ? Latest entry per `subscription_id` or latest entry in `modification_log` table? — nice_dev, Apr 11 '19 at 13:08

Jeffy Mathew · Accepted Answer · 2019-04-11T14:05:59.857

3

You have to use a subquery for taking left join with modification_log table as

SELECT 
  usp.user_id AS userId,
  usp.create_time AS create_time,
  ml.amount AS amount      
FROM user_subscription_plan AS usp
  RIGHT JOIN product AS product ON product.id = usp.product_id            
  LEFT JOIN 
        (select * modification_log where subscription_id 
        IN ('447482') order by created_at desc LIMIT 1)
        AS ml ON ml.subscription_id = usp.id         
WHERE usp.id IN ('447482')

Note that the where clause in subquery select * modification_log where subscription_id IN ('447482') is the same as with the last where condition

edited Apr 11 '19 at 14:05

answered Apr 11 '19 at 13:06

Jeffy Mathew

570
4
16

1

Note that you need to add a `order by id desc` clause. – nice_dev Apr 11 '19 at 14:00
It's better to have order by created_at desc. Adding it! – Jeffy Mathew Apr 11 '19 at 14:05

nice_dev · Answer 2 · 2019-04-11T13:25:19.527

1

Just add a max condition after your left join to get the latest entry to be joined, like below-

LEFT JOIN modification_log AS ml ON ml.subscription_id = usp.id
where usp.id IN ('447482') and ml.id = (select max(id) from modification_log)

edited Apr 11 '19 at 13:25

answered Apr 11 '19 at 13:12

nice_dev

17,053
2
21
35

I get: Error Code: 1111. Invalid use of group function – Amiga500 Apr 11 '19 at 13:15
@VedranMaricevic. Updated. We need to use `having` since it's an aggregate function. – nice_dev Apr 11 '19 at 13:17
Now it complains: Unknown column ml.id in Having clause :( – Amiga500 Apr 11 '19 at 13:19
@VedranMaricevic. Arghh!! [Having clause](https://stackoverflow.com/questions/36105812/unknown-column-in-having-clause) requires columns to be in group by or select clause. Sorry for that. I fixed my query. – nice_dev Apr 11 '19 at 13:25
@VedranMaricevic. Can you share test data, your expected result and the output you are receiving? – nice_dev Apr 11 '19 at 13:41
The answer from Jeffy Mathew works as expected, but repetition of IN clause seems redundant – Amiga500 Apr 11 '19 at 13:42
@VedranMaricevic. But how does it ensure the latest entry of `modification_log`? – nice_dev Apr 11 '19 at 13:46
I have modified it slightly with: ORDER BY id DESC LIMIT 1 – Amiga500 Apr 11 '19 at 13:46
@VedranMaricevic. ok, mine works the same way. P.S- You can accept his answer. I am just wondering why mine wouldn't work. – nice_dev Apr 11 '19 at 13:48
I was wanting to accept your answer, but it did not work :( – Amiga500 Apr 11 '19 at 13:49
1

@VedranMaricevic. Never mind. For me, it's the purpose first than acceptance. Not sure why mine wouldn't work. If you can provide some data set and expected output, I could debug. – nice_dev Apr 11 '19 at 13:50

Selecting only last record inside the JOIN statement

2 Answers2