Create group column with values based on join

Question

I have two tables 1) a customer table 2)Account table. I want to see what accounts are primary and which are secondary accounts.

In one table I have accountRowId and AccountNumber. In the other table I have PrimaryAccountRowId and SecondaryAccountRowId.

For my output I would like to have all AccountNumbers in one column with all the AccountRelationship(primary or seconday) in another column beside each AccountNumber.

In order to join table, for PrimaryAccounts I would join AccountRowId on PrimaryAccountRowId and for secondary Accounts I would just flip flop and instead of having the primaryAccountRowId it would be SecondaryAccountRowId.

My Account table:

  AccountRowId = 256073
  AccountNumber = 8003564
  AccountRowId = 342300
  AccountNumber = 2034666

Customer table:

   PrimaryAccountRowId = 256073
   SecondaryAccountRowId = 342300

What I want to see my table look like

     AccoundNumber        AccountRelationship
      8003564             Primary
      2034666             Secondary

Please provide some helpful logic/code of how I would achieve these results. Thanks

Answer 1

This can be achieved using a left join between the two tables. Basically by checking if the accountRowid exists in the column primaryAccountRowId from customer table you would know if the account_number is a primary, similarly the logic for secondary as well

eg:

   select a.accountNumber
         ,max(case when p_acct.PrimaryAccountRowId is not null then 'PRIMARY' 
                   when sec_acct.PrimaryAccountRowId is not null then 'SECONDARY'
               end) as acct_relationship
     from account a
left join customer p_acct
       on a.AccountRowId =p_acct.PrimaryAccountRowId 
left join customer sec_acct
       on a.AccountRowId =sec_acct.PrimaryAccountRowId 
 group by a.accountNumber