Exclude one item with different corelated value in the next column SQL-CodePudding

I have two tables:

acc_num	ser_code
1	A
1	B
1	C
2	C
2	D

and the second one is:

ser_code	value
A	5
B	8
C	10
D	15

I want to exclude all the accounts with the service codes that they have value of 10 or 15. Because my data set is huge, I want to use NOT EXIST but it just excludes combination of acc_num and ser_code. I want to exclude the acc_num with all of it's ser_code, because on of it's ser_code meats my criteria.

I used:
select acc_num, ser_code
from table 1
where NOT EXIST (select 1
FROM table 2 where acc_num = acc_num and value in (10, 15)

out put with above code is:

acc_num	ser_code
1	A
1	B

Desire out put would be empty.

CodePudding user response：

here you are

select t1.acc_num,t1.ser_code from table1 t1, table2 t2 
where (t1.ser_code=t2.ser_code and  t2.value not in (10,15)) 
and t1.acc_num  not in 
(
    select t3.acc_num from table1 t3,table2 t4 
    where t1.acc_num=t3.acc_num and t3.ser_code=t4.ser_code 
    and  t4.value  in (10,15)
) ;

CodePudding user response：

This could be achieved in many ways. However using NOT EXISTS is the best option. The problem with your query is for acc_num 1, there are ser_code that does not have value as 10, 15. So you will get A and B in result.

To overcome that you must pull acc_num inside the sub-query

Query 1 (using NOT EXISTS):

As you can see in the below query, I have included acc_num inside sub-query, so that the filter works properly,

SELECT DISTINCT a.acc_num, a.ser_code 
FROM one as a
WHERE NOT EXISTS
       (
       SELECT DISTINCT one.acc_num 
       FROM two 
       INNER JOIN one
           ON one.ser_code=two.ser_code
       WHERE value IN (10,15) AND a.acc_num=one.acc_num
       )

Query 2 (using LEFT JOIN):

NOT EXISTS often confusing due to it's nature (super fast though). Hence LEFT JOIN could also be used (expensive than NOT EXISTS),

SELECT DISTINCT a.acc_num, a.ser_code 
FROM one as a
LEFT JOIN 
       (
       SELECT DISTINCT one.acc_num 
       FROM two 
       INNER JOIN one
           ON one.ser_code=two.ser_code
       WHERE value IN (10,15)
       ) b
   ON a.acc_num=b.acc_num
WHERE b.acc_num IS NULL

Query 3 (using NOT IN):

NOT IN would also achieve this with comprehensive query but expensive than both of the above methods,

SELECT DISTINCT a.acc_num, a.ser_code 
FROM one as a
WHERE a.acc_num NOT IN
       (
       SELECT DISTINCT one.acc_num 
       FROM two 
       INNER JOIN one
           ON one.ser_code=two.ser_code
       WHERE value IN (10,15)
       )

All 3 would yield same result. I would prefer to go with NOT EXISTS

See demo with time consumption in db<>fiddle