CODEWITHSUNDEEP
mysql
alphy
alphy

Reputation: 291

Join two tables by finding max row first

I want to get data only for the max row for table m and join it to table l to find progress

Table(m)

   id  labref  activity
    ---------------------
    1    x       ab
    2    x       cd
    3    x       ef
    4    y       jk
    5    y       il

Table (l);

    id  activity  progress
    ---------------------
    1    ab       0
    2    cd       10
    3    ef       20
    4    jk       30
    5    il       40

When I run to get max row it works

select t1.* from m t1
left join m t2 on t1.labref = t2.labref
and t1.id < t2.id
where t2.id is null

But when i run the below query to add the progress

select t1.id, t1.labref, t1.activity, sp.progress from m t1,  l sp
left join m t2 on t1.labref = t2.labref
and sp.location = t1.activity
and t1.id < t2.id
where t2.id is null

The error is:  Unknown column 'sp.progress' in 'field list'

The result I am looking for is

id   labref   activity   progress
----------------------------------
3      x          ef       20
5      y          il       40

Any Suggestion guys, Thanks in Advance.

Upvotes: 1

Views: 58

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

SELECT m.id, m.labref, m.activity, l.progress
    FROM (SELECT labref, MAX(id) as MaxId
              FROM m
              GROUP BY labref) q
        INNER JOIN m 
            ON q.MaxId = m.id
        INNER JOIN l
            ON m.activity = l.activity;

Upvotes: 2

Related Questions