Reputation: 291
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
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