I am a little stuck on a fairly simple sqlite query - perhaps because I've been looking at the issue for several hours and need a break.
I have 2 tables (lets assume transactions only deals with incoming funds for now):
db.run(`CREATE TABLE accounts (
sort TEXT NOT NULL,
account TEXT NOT NULL,
name TEXT NOT NULL,
balance REAL DEFAULT 0.00,
PRIMARY KEY(routing, account)
);`);
db.run(`CREATE TABLE transactions (
id TEXT PRIMARY KEY NOT NULL,
to_sort TEXT NOT NULL,
to_account TEXT NOT NULL,
amount REAL NOT NULL,
currency TEXT DEFAULT "USD"
);`);
and just for this example, lets say I have the following in my accounts table:
sort: 123 account: 123456
sort: 456 account: 456789
sort: 789 account: 789100
I am able to select transactions for individual accounts fairly easily. I just do a "select * from transactions where to_sort = '123' and to_account='123456'"
The transactions table count potentially have lots of transactions, some of which are valid and related to the above accounts. Some of which may be from random accounts (i.e. sort: 999, account: 99999)
My question is pertaining to the following scenarios: I want to be able to select all transactions that are related to all accounts in the accounts table. For example I want every every transaction for the above 3 accounts only.
I think this would be along the lines of:
SELECT * from transactions, accounts
WHERE transactions.to_sort = accounts.sort
AND transactions.to_account = accounts.account
ORDER BY
transactions.to_sort ASC, transactions.to_account ASC
I want to be able to select all transaction that are NOT related to the accounts in the accounts table. I'm a little confused by this as the primary key in the accounts table is a composite key. For a single column key, I could do a "...where id not in (select id from accounts)". But I'm not quite sure what to do with the tables above.
Try
WHERE NOT transactions.to_sort = accounts.sort