Forum Discussion
Mubar
Nov 18, 2022Copper Contributor
how can I write query that will return an account that has multiple shares in a bank but excludin a
Please I want to write a query that will return only member that have multiple share account but must not include 30 or 31 when they check it out from the source.
What I want to achieve is to filter the account that has multiple shares in their account but must not have 30 or 31
Please help me out
.
- SnowMan55Bronze Contributor
Mubar Your request is not clear, and you have not described the structure of your data. By "not include 30 or 31", are you referring to share quantities, or to account types, or something else? I'm going to assume account types.
Is your relevant data structured into two tables, including at least these columns?
Table Column Member MemberID MemberName Account AccountID MemberIDOfOwner AccountType AccountShares If so, then such a query might be:
SELECT M1.MemberID, M1.MemberName, A1.AccountID FROM Member AS M1 INNER JOIN Account AS A1 ON A1.MemberIDOfOwner = M1.MemberID WHERE A1.AccountShares > 1 AND NOT EXISTS (SELECT 1 FROM Member AS M2 INNER JOIN Account AS A2 ON A2.MemberIDOfOwner = M2.MemberID WHERE M2.MemberID = M1.MemberID AND A2.AccountType IN (30, 31) ) ;
(The splitting to multiple lines and indentation are not required but I included them for ease of reading.)
- MubarCopper ContributorThank you
I got the solution later.