How to ‘create’ NULL data in Teradata SQL for non existing relations

I have 2 tables, one lists features with a feature value that an account might or might not have (TBL_Feat), the other lists the accounts (TBL_Acct).

I’m looking for a query to give me all features for every account, and if the feature doesn’t exist for this account, a line with the feature but with NULL as value. My list of features is fixed, so that’s no concern.

Tbl_Feat
FEATURE_ID  FEATURE_VALUE  ACCOUNT_NBR
1           3              100
1           4              101
1           6              102
2           4              102


Tbl_Acct
Account_nbr
100
101
102
103

What I’m expecting to see is a result like this:

Account_nbr  FEATURE_ID  FEATURE_VALUE
100          1           3
100          2           null
101          1           4
101          2           null
102          1           6
102          2           4
103          1           null
103          2           null

One adittional question, would anything change to your answer if there is a feature that is not prevalent in the Tbl_Feat table? Eg. FEATURE_ID = 3 in my example here.

Answer

Use a cross join to generate the rows and left join to bring in the values:

select a.account_nr, f.feature_id, tf.feature_value
from tbl_acct a cross join
     (select distinct feature_id from tbl_feat) f left join
     tbl_feat tf
     on tf.account_nbr = a.account_nbr and
        tf.feature_id = f.feature_id
order by a.account_nr, f.feature_id;

Advertisement

Answer