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:

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:

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.

CodePudding user response：

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.

CodePudding user response：

Let us first change all the conditions to exclude and try to select items not in the excluded list.

SELECT
    j.jev_no,
    j.account_code,
    j.debit,
    j.credit
FROM
    journal_entry j
WHERE
    j.account_code NOT IN(
    SELECT
        account_code
    FROM
        journal_entry
    WHERE
        (
            account_code = 10102010 AND j.credit <= 0
        ) OR account_code = 20201010 OR(
            account_code = 20201070 AND j.credit <= 0
        ) OR(
            account_code = 50299990 AND j.credit > 0
        ) OR(
            account_code = 50213040 AND j.credit > 0
        ) OR(
            account_code = 10305010 AND j.credit > 0
        )
) AND jev_no = '2022-03-001'