1

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.

  • @NelsonCasanova The table is a one to many relationship with the table that contains the IDs. So kind of like banking one person can have multiple accounts. – RBCunhaDesign Oct 9 '17 at 17:13
  • What's the primary key of Table_A? Can there be more than two rows with a given people ID? If so, should they only be displayed if all rows have bal <= 0, or if at least two rows have bal <= 0? if not all rows need to have bal <= 0, should all rows be displayed, or only those with bal <= 0? – RDFozz Oct 9 '17 at 17:28
3
SELECT * 
FROM [Table_A]
WHERE [ID] IN
(
    SELECT [ID]
    FROM [Table_A]
    GROUP BY [ID]
    HAVING (
            COUNT(*)=1 AND 
            MAX(CASE WHEN CODE IN ('AR', 'RT') 
                AND BAL <= 0 THEN 1 ELSE 0 END)=1
            ) 
            OR 
            (
            COUNT(*)>1 AND MAX(BAL)<=0
            )
)
  • Nice! I would never thought to do it this way, but that's what I like about programming. There are always 100 different ways of solving the same problem. – RBCunhaDesign Oct 10 '17 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.