How to filter rows by equal values in different columns for groups in SQL-CodePudding

I'm struggling on how can I filter my table when I have for the same company the same value in Cred and Debt. They need to be identic, as the toy example below.

Suppose the following table

 --------- ---------- ---------- ---------- 
| Company | Cred     | Debt     | ID       |
 --------- ---------- ---------- ---------- 
| BFG     |       10 |         0|         1|
| AAA     |       11 |         0|         2|
| CCC     |        6 |         0|         3|
| BFG     |        0 |        13|         4|
| AAA     |        0 |        11|         5|
| CCC     |        0 |         7|         6|
| CCC     |        0 |         6|         7|
 --------- ---------- ---------- ---------- 


-- Desired result, as something like
 --------- ---------- ---------- ---------- 
| Company | Cred     | Debt     | ID       |
 --------- ---------- ---------- ---------- 
| AAA     |       11 |         0|         2|
| CCC     |        6 |         0|         3|
| AAA     |        0 |        11|         5|
| CCC     |        0 |         6|         7|
 --------- ---------- ---------- ----------

I thought of using their sum and filtering in the column the total that had equal values, grouping by total and company, but this strategy didn't work, since the company can have equal values in the debit column in different rows.

Any hint on how can I do that?

CodePudding user response：

You can use EXISTS:

SELECT t1.*
FROM tablename t1
WHERE EXISTS (
  SELECT 1
  FROM tablename t2
  WHERE t2.Company = t1.Company AND t2.ID <> t1.ID
    AND t2.Cred = t1.Debt AND t2.Debt = t1.Cred
);

If Cred and Debt are always different in the same row you can remove the condition AND t2.ID <> t1.ID.

CodePudding user response：

select a.* 
from sometable a, sometable b
where a.id <> b.id
and a.company=b.company
and a.credit=b.debt
and a.debt = b.credit;

CodePudding user response：

with select company comp, credit cred , Id credit_id from tablename As credit Select company,debit,cred,credit_id,debit_id From Credit join tablename on company = comp Where cred = debit

If you have too many matching items you will have a problem as the number of rows will be the product of the number of matching items