how can I write query that will return an account that has multiple shares in a bank but excludin a

New Contributor

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

 

.

2 Replies

@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?

TableColumn
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.)