Why isn't my SQL WHERE clause being respected?-CodePudding

SELECT * FROM "transaction"
WHERE type = {transaction_type} AND 
(from_member_id AND to_member_id) IN (SELECT member_id FROM org WHERE org_member = {org.id})

I'm finding that the clause where I check that both the from_member_id and to_member_id is in the org that I want isn't actually being respected.

For clarity; I want to select all transactions that are between members in the same organisation.

Further, I would expect that my union would work too (as member is the same member referenced in org_member)

SELECT * FROM "transaction"
INNER JOIN member ON "transaction.member_id" = member.id
WHERE type = {transaction_type} AND 
("transaction.from_member_id" AND "transaction.to_member_id") IN (SELECT member_id FROM org WHERE org_member = {org.id})

Any help would be greatly appreciated.

Also, NOTE: The {} syntax as I string interpolate in python, so please ignore.

CodePudding user response：

In the expression:

(from_member_id AND to_member_id) IN (...)

the first part will be treated as a boolean expression with possible values 1, 0 and null. So it won't produce the expected result.

You need to check them both separately:

AND transaction.from_member_id IN (SELECT member_id FROM org WHERE org_member = {org.id})
AND transaction.to_member_id   IN (SELECT member_id FROM org WHERE org_member = {org.id})