SQLite selecting transactions that do / do not meet a particular criteria

Question

I am trying to extract data from a GnuCash SQLite database. Relevant tables include accounts, transactions, and splits. Simplistically, accounts contain transactions which contain splits, and each split points back to an account.

The transactions need to be processed differently depending on whether each one does or does not include a particular kind of transaction fee—in this case whether or not the transaction contains a split linked to account 8190-000.

I've set up two queries, one that handles transactions with the transaction fee, and one that handles transactions without the transaction fee. The queries work, but they are awkward and wordy, and I'm sure there is a better way to do this. I did see not exists in this answer, but could not figure out how to make it work in this situation.

My current queries look like this:

-- Find all transactions containing a split with account code 8190-000
select tx_guid from transactions
inner join
    (select tx_guid from
        (splits inner join accounts on splits.account_guid = accounts.guid)
        where accounts.code = "8190-000") fee_transactions
    on fee_transactions.tx_guid = transactions.guid;

-- Find all transactions not containing a split with account code 8190-000
select guid from transactions
except
select tx_guid from transactions
inner join
    (select tx_guid from
        (splits inner join accounts on splits.account_guid = accounts.guid)
        where accounts.code = "8190-000") fee_transactions
    on fee_transactions.tx_guid = transactions.guid;

Given that I need to use these results in other queries, what is a simpler and more succinct way to obtain these lists of transactions?

Answer 1

You can use EXISTS for your 1st query like this:

SELECT t.* 
FROM transactions t
WHERE EXISTS (
  SELECT 1 
  FROM splits s INNER JOIN accounts a
  ON s.account_guid = a.guid
  WHERE a.code = '8190-000' AND ?.tx_guid = t.guid
);

Change ? to s or a, depending on which table contains the column tx_guid (splits or accounts), since it is not clear in your question.

Also, change to NOT EXISTS for your 2nd query.