In a nested query, check all values for a condition

Question

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

Could you post a data sample and desired output (both as text)? — Zakaria, Mar 03 '22 at 14:10

Salman A · Accepted Answer · 2022-03-03T14:30:58.037

2

You can write the conditions as a where clause:

select *
from conclusionCard_Files
where exists (
    select *
    from agreement
    where agreement.conclusionCardFile = conclusionCard_Files.id
    having sum(case when status = 1 then 1 else 0 end) > 0
    and    sum(case when status = 1 then 0 else 1 end) = 0
)

edited Mar 03 '22 at 14:30

answered Mar 03 '22 at 14:24

Salman A

262,204
82
430
521

This one worked, thanks – zig8953 Mar 04 '22 at 08:53

D-Shih · Answer 2 · 2022-03-03T14:31:07.640

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

EDIT

if you want to get data from ConclusionCard_Files based on your condition. you can try to let condition aggregate function in subquery each ConclusionCardFile from table Agreement then do JOIN

SELECT f.*
FROM (
    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,
           a.ConclusionCardFile
    FROM Agreement a 
    GROUP BY a.ConclusionCardFile
) a 
INNER JOIN ConclusionCard_Files f
ON a.ConclusionCardFile = f.id
WHERE a.agreed > 0 AND a.dis_agreed = 0

@SalmanA You might right OP seem like want to get data from `ConclusionCard_Files` table — D-Shih, Mar 03 '22 at 14:34

score 1 · Answer 3 · answered Mar 03 '22 at 14:14

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

SELECT 
   *
FROM ConclusionCard_Files f
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

In a nested query, check all values for a condition

3 Answers3