Forum Discussion

Mubar's avatar
Mubar
Copper Contributor
Nov 18, 2022

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

 

.

  • SnowMan55's avatar
    SnowMan55
    Bronze 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?

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

    • Mubar's avatar
      Mubar
      Copper Contributor
      Thank you

      I got the solution later.

Resources