mysqlaccounting

MySQL selecting specific values but still want to include rows that doesn't matches the condition


I am working with the database of a journal entry voucher management system. I have a journal_entry table with 517 rows. I select the table with a jev no = '2022-03-001'

SELECT
    jev_no, account_code, debit, credit
FROM
    journal_entry
WHERE
 jev_no ='2022-03-001'

and the returned rows are:

jev_no account_code debit credit
2022-03-001 50501010 800.00 0.00
2022-03-001 20201010 0.00 800.00
2022-03-001 10102010 0.00 521.00
2022-03-001 20201070 521.00 0.00

I want to filter these specific values in the table:

  1. Include account_code = 10102010 with credit > 0
  2. Exclude account_code = 20201010
  3. Include account_code = 20201070 with credit > 0
  4. Exclude account_code = 50299990 with credit > 0
  5. Exclude account_code = 50213040 with credit > 0
  6. Exclude account_code = 10305010 with credit > 0

but if a row doesn't match the condition, it should still be included in the result

Then I tried to filter the table with

SELECT
    j.jev_no, j.account_code, j.debit, j.credit
FROM
    journal_entry j
WHERE

         (account_code = 10102010 and j.credit > 0)
        AND account_code <> 20201010
        AND (account_code = 20201070  and j.credit > 0)
        AND ( account_code <> 50299990  and j.credit > 0)
        AND (account_code <> 50213040  and j.credit > 0)
        AND (account_code <> 10305010  and j.credit > 0)
and jev_no = '2022-03-001'

But there are no rows returned.

Result should be:

jev_no account_code debit credit
2022-03-001 50501010 800.00 0.00
2022-03-001 10102010 0.00 521.00

Because: The account code 20201070 should be excluded because the credit value is not greater than 0. The account code 20201010 should also be excluded.


Solution

  • Include and Exclude are mutually exclusive in this case. From your intention, you have 2 include account_code and 4 exclude account_code. What about the rest of the account_code which are not covered by those 6 ? Should you include or exclude the rest? Based on your request but if a row doesn't match the condition, it should still be included in the result ,it means you would like to include the rest, then what use are those 2 include account_code for? Why not just have the 4 exclude account_code and include the rest ? From my point of view, we should use a blacklist approach (specify the exclude account_code and include the rest) here.

    SELECT
        jev_no, account_code, debit, credit
    FROM
        journal_entry
    WHERE
     jev_no ='2022-03-001'
     and (not (  account_code = 20201010
                or ( account_code = 50299990  and credit > 0)
                or (account_code = 50213040  and credit > 0)
                or (account_code = 10305010  and credit > 0)
              )
         )
    ;
    
    -- result set:
    # jev_no, account_code, debit, credit
    2022-03-001, 50501010, 800.00, 0.00
    2022-03-001, 10102010, 0.00, 521.00
    2022-03-001, 20201070, 521.00, 0.00
    
    
    

    Last, please check your expected output, as it does not add up based on your rules.