CODEWITHSUNDEEP
sqlsql-server
Arulmurugan K
Arulmurugan K

Reputation: 83

How to fix correlated subquery wrongly selected data?

I want to select the 'batchid's from below table that batchid's all records 'subId' and 'substatus' in '23' and 'READY' respectively. if any values from 'subId' or 'substatus' is not matched '23' and 'READY' respectively then don't take that batch.

Table:


+---------+----------+--------+-------+-----------+
| batchid |   dcn    | dcnseq | subId | substatus |
+---------+----------+--------+-------+-----------+
|   10001 | 10001001 |      1 |    23 | READY     |
|   10001 | 10001001 |      2 |    23 | READY     |
|   10001 | 10001002 |      1 |    23 | READY     |
|   10001 | 10001003 |      1 |    23 | READY     |
|   10001 | 10001004 |      1 |    23 | READY     |
|   10001 | 10001004 |      2 |    23 | READY     |
|   10001 | 10001004 |      3 |    23 | READY     |
|   10002 | 10001005 |      1 |    23 | READY     |
|   10002 | 10001005 |      2 |    23 | READY     |
|   10002 | 10001006 |      1 |    23 | READY     |
|   10002 | 10001007 |      1 |    23 | READY     |
|   10002 | 10001008 |      1 |    23 | READY     |
|   10002 | 10001008 |      2 |    23 | READY     |
|   10002 | 10001009 |      1 |    23 | READY     |
+---------+----------+--------+-------+-----------+

I am using below query to achieve this requirement.

select distinct batchid from fm o
where o.subId='23' and o.substatus='READY' 
and o.dcnseq='1' and o.batchid in 
(
  select a.batchid from
  (
  select i.batchid, SUM(case when i.subId='23' and i.substatus='READY' then 0 else 1 end)match from fm i 
  where i.batchid=o.batchid
  group by i.batchid
  having SUM(case when i.subId='23' and i.substatus='READY' then 0 else 1 end)=0
  )a
)

Result:

+---------+
| batchid |
+---------+
|   10001 |
|   10002 |
+---------+

It's working perfectly. Now changed 'substatus' value for one records as 'HOLD' 


+---------+----------+--------+-------+-----------+
| batchid |   dcn    | dcnseq | subId | substatus |
+---------+----------+--------+-------+-----------+
|   10001 | 10001001 |      1 |    23 | HOLD      |
|   10001 | 10001001 |      2 |    23 | READY     |
|   10001 | 10001002 |      1 |    23 | READY     |
|   10001 | 10001003 |      1 |    23 | READY     |
|   10001 | 10001004 |      1 |    23 | READY     |
|   10001 | 10001004 |      2 |    23 | READY     |
|   10001 | 10001004 |      3 |    23 | READY     |
|   10002 | 10001005 |      1 |    23 | READY     |
|   10002 | 10001005 |      2 |    23 | READY     |
|   10002 | 10001006 |      1 |    23 | READY     |
|   10002 | 10001007 |      1 |    23 | READY     |
|   10002 | 10001008 |      1 |    23 | READY     |
|   10002 | 10001008 |      2 |    23 | READY     |
|   10002 | 10001009 |      1 |    23 | READY     |
+---------+----------+--------+-------+-----------+

Now result is: 

+---------+
| batchid |
+---------+
|   10002 |
+---------+

Now its also working correctly. But sometimes also picking '10001' for same case. its occurred when tables have lot of batchid. I try to understand mistake. But I can't able to find out.

Upvotes: 0

Views: 73

Answers (3)

forpas
forpas

Reputation: 164089

The simplest solution is with NOT EXISTS:

select distinct f.batchid
from fm f
where not exists (
  select 1 from fm
  where batchid = f.batchid and (coalesce(subid, 0) <> 23 or coalesce(substatus, '') <> 'READY')
)

coalesce() is needed only for the case there may exist nulls in the columns subId and substatus.
If there are not any nulls then the where clause can be simplified to:

where batchid = f.batchid and (subid <> 23 or substatus <> 'READY')

See the demo.
Results:

> | batchid |
> | ------: |
> |   10001 |
> |   10002 |

Upvotes: 0

Luuk
Luuk

Reputation: 14920

Selecting batchid's where (subid=23 and substatus = 'READY'), and no other values for subid and/or substatus exist for that batchid.

select batchid 
from fm 
where subId=23 and substatus='READY' 
group by batchid
except
select batchid 
from fm 
where not(subId=23 and substatus='READY' )
group by batchid

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think your query is too complicated. Just use aggregation and having:

select batchid
from fm 
group by batchid
having min(subid) = max(subid) and max(subid) = 23 and
       min(substatus) = max(substatus) and max(substatus)= 'READY';

I don't know if your other conditions are important. They are in your query but not mentioned in the question.

Upvotes: 2

Related Questions