Vijay pandey Vijay pandey - 1 year ago 56
MySQL Question

Fetch only those records from table join which has not reached its final state

if have two tables A and B:

Table A
id work_name
1 10001
2 10002


Table B
id work_id final_status
1 1 0
2 2 0
3 1 1

i have to fetch only those work_id whose final status is not 1
In Above Table output should 2 as final_status of work_id 1 is reached to 1.
I have tried the Query but it is not giving me desired result

My Query:

select B.work_id from A inner join B on = B.work_id where final_status!=1

My Result:


I want to exclude 1 as it has reach to its final status.

Answer Source

There are multiple ways to produce the required output:

  • join table B twice on table A
  • join table B once, get the max() of final_status and filter in having clause
  • combination of in() and not in() subqueries
  • combination of exists() and not exists() subqueries

I prefer the last method because exists() / not exists() do not have to actually fetch the data from table B:

select A.*
from A
where exists(select 1 from B where and B.final_status=0)
    and not exists(select 1 from B where and B.final_status=1)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download