Vijay pandey Vijay pandey - 21 days ago 5
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


and

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 A.id = B.work_id where final_status!=1


My Result:

1
2


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

Answer

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 B.work_id=A.id and B.final_status=0)
    and not exists(select 1 from B where B.work_id=A.id and B.final_status=1)
Comments