In a nested query, check all values for a condition-CodePudding

How can I get rid of nested queries (agree, dis_agreed)? How to rewrite to join - I can not think of it. Maybe there are other optimal solutions?

select * from 
(select 
    (select count(id) from  Agreement a where (a.ConclusionCardFile = f.id) and a.Status =1) agreed,
    (select count(id) from  Agreement a where (a.ConclusionCardFile = f.id) and (a.Status != 1 or a.Status is null)) dis_agreed
from ConclusionCard_Files f) t
where t.agreed > 0 and dis_agreed = 0

CodePudding user response：

If I understand correctly, you can try to use JOIN with HAVING condition aggregate function.

SELECT COUNT(CASE WHEN a.Status = 1 THEN ID END) agreed,
       COUNT(CASE WHEN a.Status <> 1 or a.Status is null THEN ID END) dis_agreed
FROM Agreement a 
INNER JOIN ConclusionCard_Files f
ON a.ConclusionCardFile = f.id
HAVING 
    COUNT(CASE WHEN a.Status = 1 THEN ID END) > 0
AND 
   COUNT(CASE WHEN a.Status <> 1 or a.Status is null THEN ID END) > 0

CodePudding user response：

Maybe you just using sub-queries only to filter? What about to move them to WHERE clause?

SELECT 
   *
FROM ConclusionCard_Files f t
WHERE 
    EXISTS(select * from  Agreement a where (a.ConclusionCardFile = f.id) and a.Status =1)
    AND NOT EXISTS(select * from  Agreement a where (a.ConclusionCardFile = f.id) and (a.Status != 1 or a.Status is null))

It's performance friendly because SqlServer do not count all Counts