CODEWITHSUNDEEP
phpmysql
Jithin Varghese
Jithin Varghese

Reputation: 2228

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.

Upvotes: 1

Views: 63

Answers (2)

Danilo Bustos
Danilo Bustos

Reputation: 1093

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

Upvotes: 1

Agustin Dortona
Agustin Dortona

Reputation: 111

Try this

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 2) 
uu ON uu.assign_id = a.assign_id WHERE a.central_status = 1 ORDER BY 
a.assign_id DESC

With LIMIT 1 you are only comparing with the last row from tbl_unit_status which has assign_id 1.

Upvotes: 0

Related Questions