So here's the deal. I have a table with the following sample data.
Edit: This table has a many-to-one relationship with the "PEOPLE"
table.
Table_A
ID |CODE |TYPE | BAL
-----|-------|-------|------
1 |AR |FO |0.00
-----|-------|-------|------
1 |RT |CM |335.00
-----|-------|-------|------
2 |AR |CU |0.00
-----|-------|-------|------
3 |RT |CM |167.00
-----|-------|-------|------
4 |AR |CU |500.00
-----|-------|-------|------
4 |RT |CM |0.00
-----|-------|-------|------
5 |RT |CM |0.00
-----|-------|-------|------
6 |AR |FO |200.00
-----|-------|-------|------
7 |AR |CU |0.00
-----|-------|-------|------
7 |RT |CM |0.00
I need to select all records in which if they only have one row then CODE IN ('AR', 'RT') AND BAL <= 0
, but if they have two rows then I only need the ones that both BAL
columns are <= 0
.
Example:
The query should return:
Edit: Later I will add the DISTINCT
to the query, but for now I'm just curious on how to get the records.
ID |CODE |TYPE | BAL
-----|-------|-------|------
2 |AR |CU |0.00
-----|-------|-------|------
5 |RT |CM |0.00
-----|-------|-------|------
7 |AR |CU |0.00
-----|-------|-------|------
7 |RT |CM |0.00
So pretty much I need to evaluate whether or not the BAL <= 0
and if they have two rows than both rows should have BAL <= 0
.
I've tried different WHERE
clauses with no luck. Let me know if you all need more information and I can add it here.
Best Answer