mysql query not working as expected complex

Question

I have 3 tables in which all 3 tables are joined to get the result.

Below is the table,

//tbl_order

order_id    order_no_first    order_no    order_no_last    order_date
---------------------------------------------------------------------
   1              C             1000            a          2017-05-16
   2              C             1001            a          2017-05-16
   3              C             1001            b          2017-05-16
   4              A             1002            a          2017-05-16
   5              A             1002            b          2017-05-16

//tbl_assign

assign_id   order_id   order_no_first   central_status   central_assign_unit
----------------------------------------------------------------------------
    1          1             C                1                   1
    2          2             C                1                   1
    3          3             C                1                   1
    4          4             A                1                   1

//tbl_unit_status

status_id    assign_id    status_status
---------------------------------------
    1           1             Cutter
    2           1             Stitch
    3           1             Delivery
    4           2             Cutter
    5           2             Stitch
    6           3             Cutter
    7           4             Cutter

I want the result as below,

//Required output

order_id  assign_id  order_no_first  order_no  order_no_last  status_status
---------------------------------------------------------------------------
    2         2            C           1001          a           Stitch
    3         3            C           1001          b           Cutter
    4         4            A           1002          a           Cutter
    5                      A           1002          b

from the table tbl_unit_status the status below status_status field, if it is Despatch then do not display that result.

I have tried to get the above result. But no success below is my code.

`SELECT * 
FROM tbl_order o 
LEFT JOIN tbl_assign a 
ON a.order_id = o.order_id AND o.order_no_first = a.order_no_first 
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 AND a.central_assign_unit = 1 
    OR (u2.status_status != "Delivery" AND u2.status_status != "Despatch") 
GROUP BY o.order_id

From the above code, the result is

//wrong output

order_id  assign_id  order_no_first  order_no  order_no_last  status_status
---------------------------------------------------------------------------
    1         1            C           1000          a           Delivery
    2         2            C           1001          a           Stitch
    3         3            C           1001          b           Cutter
    4         4            A           1002          a           Cutter
    5                      A           1002          b

Is there any way to get the Required output as soon in the first output. I have tried and am stuck in here. Thank you.

Explain what do you want and why your output is not correct

Ashraful Islam
– Ashraful Islam

2017-05-19 19:08:53 +00:00
Commented May 19, 2017 at 19:08 — Ashraful Islam
– Ashraful Islam, Commented May 19, 2017 at 19:08
See meta.stackoverflow.com/questions/333952/…

Strawberry
– Strawberry

2017-05-19 20:54:01 +00:00
Commented May 19, 2017 at 20:54 — Strawberry
– Strawberry, Commented May 19, 2017 at 20:54

Diéfani Favareto Piovezan · Accepted Answer · 2017-05-19 20:25:59Z

1

Use parenthesis correctly arround AND OR clauses and I recommend using NOT LIKE instead of != when dealing with strings.

SELECT * 
FROM tbl_order o 
LEFT JOIN tbl_assign a 
ON a.order_id = o.order_id AND o.order_no_first = a.order_no_first 
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 AND (a.central_assign_unit = 1 OR u2.status_status NOT LIKE 'Delivery' AND u2.status_status NOT LIKE 'Despatch') 
GROUP BY o.order_id

Obs: I can't comment due to my reputation, I'm sorry

answered May 19, 2017 at 20:25

Diéfani Favareto Piovezan

2291 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom · Accepted Answer · 2017-05-19 19:26:39Z

0

You should be using AND instead of OR in your where clause. You don't need the parentheses either.

SELECT  * 

FROM    tbl_order o 

    LEFT JOIN tbl_assign a 
    ON a.order_id = o.order_id 
    AND o.order_no_first = a.order_no_first 

    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 
    AND a.central_assign_unit = 1 
    AND (
             u2.status_status IS NULL  
         OR
             u2.status_status NOT IN ("Delivery", "Despatch")
        )

GROUP BY o.order_id

edited May 19, 2017 at 19:26

answered May 19, 2017 at 19:16

Tom

6,6633 gold badges24 silver badges42 bronze badges

3 Comments

Jithin Varghese Over a year ago

If am using AND instead of OR then order_id = 5 will not show.

Tom Over a year ago

Try now. It will allow for a null status or any status other than Deliver or Despatch.

Strawberry Over a year ago

And you definitely don't need '*'... or that group by clause

Collectives™ on Stack Overflow

mysql query not working as expected complex

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related