CODEWITHSUNDEEP
phpmysql
Jithin Varghese
Jithin Varghese

Reputation: 2228

remove rows from one table1 if table2 contains same value with some condition mysql

I want to make a query that select all rows from a tbl_assign where tbl_unit_status does not contain some values.

//tbl_assign

assign_id    order_id    order_no_first
---------------------------------------
    1           1              C
    2           2              C
    3           10             C
    4           11             C
    5           17             C

//tbl_unit_status

status_id    assign_id    status_status
---------------------------------------
    1            1           Cutter
    2            1           QC Jithin
    3            1           Rack
    4            1           Delivery
    5            2           Cutter
    6            3           Rack
    7            4           Cutter
    8            5           Despatch

the required output is,

assign_id    order_id    order_no_first
---------------------------------------
    2           2              C
    4           11             C

I want to eliminate rows from tbl_assign where tbl_unit_status contain values Rack, Despatch, Delivery and QC%

I have tried the query,

SELECT a.assign_id, a.order_id, a.order_no_first FROM tbl_assign a WHERE a.assign_id NOT IN (SELECT u.assign_id FROM tbl_unit_status u WHERE u.status_status NOT LIKE "QC%" OR u.status_status NOT IN ("Delivery", "Despatch", "Rack"))

The above code doesn't return the required output. Someone please help me.

Upvotes: 2

Views: 52

Answers (2)

Brian Huynh
Brian Huynh

Reputation: 41

Output:

assign_id | order_id | order_no_first
----------|----------|--------------
2         | 2        | C
----------|----------|--------------
4         | 11       | C

Code:

SELECT a.assign_id,
       a.order_id,
       a.order_no_first
FROM   tbl_assign a
WHERE  a.assign_id NOT IN
(
     SELECT   u.assign_id
     FROM     tbl_unit_status u
     WHERE    u.status_status IN( 'Rack', 'Despatch', 'Delivery' )
              OR u.status_status LIKE 'QC%'
);

Try It: http://sqlfiddle.com/#!9/71b6b0/1/0

Summary: Returns all rows from the tbl_assign table where their assign_id values are not correlating to those returned from sub-select.

The sub-select returns all assign_id values of rows from the tbl_unit_status table. Their status_status begins with 'QC' or are equal to 'Rack', 'Delivery', or 'Despatch'.

Upvotes: 1

Carlos R
Carlos R

Reputation: 205

Following you query, the output is: assign_id order_id order_no_first

assign_id order_id order_no_first

1           1              C
3           10             C
5           17             C

Try this SQL:

SELECT a.assign_id, a.order_id, a.order_no_first 

FROM tbl_assign a WHERE a.assign_id NOT IN (

SELECT u.assign_id 

FROM tbl_unit_status u 

WHERE u.status_status LIKE "QC%" OR u.status_status IN ("Delivery", "Despatch", "Rack")
)
assign_id order_id order_no_first

2           2              C
4           11             C

Upvotes: 1

Related Questions