CODEWITHSUNDEEP
mysql
esandrkwn
esandrkwn

Reputation: 399

mySql: getting latest transaction made for a row from another table

EDIT: I think I got it. Will do further test tomorrow and I'll get back to this post. Thanks guys!

I'm trying to query for the last transaction made for each item on tbl_invty that doesn't have the transaction type "Idle" from table tbl_trans. The multiplicity of transactions is confusing me on getting my query right and all I was able to do so far was joining the two tables below on tbl_invty.code=tbl_trans.code. How do I go about this so I could output only rows 2 and 3 from tbl_invty joined with rows 5 and 8 from tbl_trans based on the column code?

tbl_invty

+------+-------------+
| CODE | DESCRIPTION |
+------+-------------+
|    1 |         abc |
|    2 |         bbb |
|    3 |         cdf | 
+------+-------------+

tbl_trans

+----------+------+--------+------------+
| TRANS_ID | CODE |   TYPE | TRANS_DATE |           
+----------+------+--------+------------+
|        1 |    1 |    NEW | 2012-09-29 |           
|        2 |    1 | UPDATE | 2012-09-30 |   
|        3 |    1 |   IDLE | 2012-09-30 |        
|        4 |    2 |    NEW | 2012-09-29 |   
|        5 |    2 | UPDATE | 2012-09-30 |   
|        6 |    3 |    NEW | 2012-09-29 |       
|        7 |    3 | UPDATE | 2012-09-30 |           
|        8 |    3 | UPDATE | 2012-09-30 |   
+----------+------+--------+------------+

Upvotes: 0

Views: 935

Answers (2)

7alhashmi
7alhashmi

Reputation: 924

Try something like this:

SELECT i.des, i.code, max(t.trans_id), max(t.date)
FROM tbl_invty i left join tbl_trans t
ON t.code = i.code
WHERE i.code <> 1 AND t.type <> 'IDLE'
GROUP BY i.code;

SQL Fiddle..

EDIT: I just notice what you're looking for, check this:

SELECT i.des, i.code, max(t.trans_id), max(t.date)
FROM tbl_invty i left join tbl_trans t
ON t.code = i.code
where t.code not in (select code from tbl_trans
             where type = 'IDLE')
group by i.code;

SQL Fiddle..

Upvotes: 1

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

SELECT tt.*, result.* from tbl_trans tt
INNER JOIN
(SELECT DESCRIPTION, MAX(t.TRANS_ID) as TRANS_ID ,i.`CODE`,  
 GROUP_CONCAT(t.`TYPE`) TYPES
from tbl_invty i
LEFT JOIN tbl_trans t
ON i.CODE = t.CODE
GROUP BY i.`CODE`
HAVING NOT FIND_IN_SET('IDLE',TYPES)) result
ON tt.TRANS_ID = result.TRANS_ID;

SAMPLE FIDDLE

Upvotes: 1

Related Questions