How to run a query that checks for every negative value there is also a positive value?-CodePudding

I have a table for which every user (number_id) there is a transaction.

I want to check that for each user that has a negative value, there is also a positive value (regardless if they don't match). If there is only a positive value, and no negative value, that's ok. But if there is a negative value, there should always be a positive value associated to that number_id for that item_id.

Is there I way I can check that for every user that has a negative value, there is a positive value as well?

CREATE TABLE TBL_A
(
    number_id varchar(50), 
    item_id varchar(50), 
    amount int
);

INSERT INTO TBL_A
VALUES (121144, '1232p92nx', '-10'),
       (121144, '1232p92nx', '10'),
       (121111, '1232p92nx', '20'),
       (121111, '1232p92nx', '-20'),
       (121122, '1232p92nx', '30'),
       (121122, '1232p92nx', '-15'),
       (121198, '1232p92nx', '40'),
       (121198, '1232p92nx', '-20'),
       (121200, '1232p92nx', '10');

CodePudding user response：

Use exists to check the existence of the equivalent abs absolute value of a negative number

select * from TBL_A t1
where amount < 0
and exists (
  select 1 from TBL_A t2
  where t2.number_id = t1.number_id
  and t2.amount = abs(t1.amount)
)

or a column to display if there is an equivalent

select *,(select 'Yes' from TBL_A t2
  where t2.number_id = t1.number_id
  and t2.amount = abs(t1.amount)
  and t1.amount < 0
  ) from TBL_A t1

CodePudding user response：

you can simply get the number_id's that have a negative amount but not a positive amount by query like this:

select number_id
from TBL_A
group by number_id
having count(case when amount < 0 then 1 end) > 1
and count(case when amount > 0 then 1 end) = 0

from your data its not clear , if transactions are per number_id only or number_id and item_id, if it makes sense, you may want to add item_id to your group by statement