Jithin Varghese Jithin Varghese -4 years ago 87
MySQL Question

selecting all row from two tables and one row from last table mysql

I want to select all row from two tables and one row from last table mysql

My table is given below,

tbl_order

order_id order_no
-------- --------
1 1000
2 1001
3 1002

tbl_assign

assign_id order_id central_status
--------- -------- --------------
1 1 1
2 2 1
3 3 1

tbl_unit_status

status_id assign_id status_status
--------- --------- -------------
1 1 Work
2 2 Cutter
3 2 Stitch
4 1 Stitch


From the above 3 table, I want the result as,

order_id order_no assign_id status_status
-------- -------- --------- -------------
3 1002 3 {null}
2 1001 2 Stitch
1 1000 1 Stitch


I have tried the below code,

SELECT * FROM tbl_order o LEFT JOIN tbl_assign a ON a.order_id = o.order_id LEFT JOIN (SELECT * FROM tbl_unit_status u ORDER BY u.status_id DESC LIMIT 1) uu ON uu.assign_id = a.assign_id WHERE a.central_status = 1 ORDER BY a.assign_id DESC


But the result comes as,

order_id order_no assign_id status_status
-------- -------- --------- -------------
3 1002 3 {null}
2 1001 2 {null}
1 1000 1 Stitch


Where am doing wrong. I have tried a lot. Please help me find the answer. Thank you.

Answer Source

try like this:

SELECT o.*,u2.assign_id,u2.status_status FROM tbl_order o 
LEFT JOIN tbl_assign a ON a.order_id = o.order_id LEFT JOIN 
(SELECT u.assign_id,max(u.status_id) as maxid FROM tbl_unit_status u  group by u.assign_id) 
uu ON uu.assign_id = a.assign_id
LEFT JOIN tbl_unit_status  u2 on u2.status_id = uu.maxid
 WHERE a.central_status = 1 ORDER BY a.assign_id DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download