question

I have 2 tables now. I want to add one column to table 1 to compare whether fundcode from table1 can be found in accountcode in table 2. If it could be found, it's true. If not, then false. I want to use something like"join" to create a permanent table.. table1: SELECT TOP (1000) [list_tag] ,[fundcode] ,[added_date] ,[updated_date] FROM [aci_invest].[corfi].[fund_list] table2: Select i.issuer_name as ISSUER_NAME, f.fundcode as ACCOUNT_CODE, i.issue_description as ISSUE_DESCRIPTION, i.coupon_div_currency as INCOME_CURRENCY, p.local_mv_accint as MARKET_VALUE_INCOME, p.effective_date as VALUE_DATE, ice.pf_class_level6 as DETAIL_CLASS_LEVEL_8_EN, i.issuer_name_en as ISSUER_NAME_EN, i.parent_issuer_name_en as PARENT_ISSUER_NAME_EN from main.vw_position p join main.vw_fund f on p.effective_date=f.effective_date and p.fundcode=f.fundcode join main.vw_instrument i on p.effective_date=i.effective_date and p.instrument_id=i.instrument_id join main.vw_instrument_analytic ia on p.effective_date=ia.effective_date and p.instrument_id=ia.instrument_id join main.vw_instrument_class_en ice on p.effective_date=ice.effective_date and p.instrument_id=ice.instrument_id left join main.vw_instrument_rating ir on p.effective_date=ir.effective_date and p.instrument_id=ir.instrument_id where p.effective_date = '2018-01-18' and (ice.pf_class_level1 = 'Cash' or ice.pf_class_level1 = 'Fixed Income') and (p.lt_flag = 'S' or p.lt_flag = 'SL') and (ice.pf_class_level2 = 'Bonds' or ice.pf_class_level2 = 'Money Market' or ice.pf_class_level2 = 'CASH' OR ice.pf_class_level2 = 'Mortgages' or ice.pf_class_level2 = 'OTHER') and ice.pf_class_level5 = 'Corporate' and (p.fundcode like 'A%' or p.fundcode like 'F%' or p.fundcode like 'X%') What I wrote is : ( but it is the temporary select.. I want it to be permanent) WITH cte AS ( SELECT i.issuer_name AS ISSUER_NAME , f.fundcode AS ACCOUNT_CODE , i.issue_description AS ISSUE_DESCRIPTION , i.coupon_div_currency AS INCOME_CURRENCY , p.local_mv_accint AS MARKET_VALUE_INCOME , p.effective_date AS VALUE_DATE , ice.pf_class_level6 AS DETAIL_CLASS_LEVEL_8_EN , i.issuer_name_en AS ISSUER_NAME_EN , i.parent_issuer_name_en AS PARENT_ISSUER_NAME_EN FROM main.vw_position p JOIN main.vw_fund f ON p.effective_date = f.effective_date AND p.fundcode = f.fundcode JOIN main.vw_instrument i ON p.effective_date = i.effective_date AND p.instrument_id = i.instrument_id JOIN main.vw_instrument_analytic ia ON p.effective_date = ia.effective_date AND p.instrument_id = ia.instrument_id JOIN main.vw_instrument_class_en ice ON p.effective_date = ice.effective_date AND p.instrument_id = ice.instrument_id LEFT JOIN main.vw_instrument_rating ir ON p.effective_date = ir.effective_date AND p.instrument_id = ir.instrument_id WHERE p.effective_date = '2018-01-18' AND ( ice.pf_class_level1 = 'Cash' OR ice.pf_class_level1 = 'Fixed Income' ) AND ( p.lt_flag = 'S' OR p.lt_flag = 'SL' ) AND ( ice.pf_class_level2 = 'Bonds' OR ice.pf_class_level2 = 'Money Market' OR ice.pf_class_level2 = 'CASH' OR ice.pf_class_level2 = 'Mortgages' OR ice.pf_class_level2 = 'OTHER' ) AND ice.pf_class_level5 = 'Corporate' AND ( p.fundcode LIKE 'A%' OR p.fundcode LIKE 'F%' OR p.fundcode LIKE 'X%' ) ) SELECT TOP ( 1000 ) [list_tag] , [fundcode] , [added_date] , [updated_date] , ( CASE WHEN EXISTS ( SELECT * FROM cte WHERE accountcode = [aci_invest].[corfi].[fund_list].fundcode ) THEN 1 ELSE 0 END ) FROM [aci_invest].[corfi].[fund_list]