SQL server - joins on multiple columns

[darylmc]

Hi All,

I'm attempting to join tables on multiple columns using SQL. For some reason, it doesn't like when attempt to join table d on multiple columns (fine if just one). What is the correct code I should be using?

SELECT
	ART_HIER_LEV3_NAME BU,
	SITE_NAME,
	NATURAL_DATE_DTE,
	ARTICLE_ID,
	site_id,
	DAY_OF_THE_WEEK,
	PRICE_TYPE,
	SUM(STK_POSTN_SNPSHT) STKPOSTNSNPSHTSUM,
	SUM(BACKSTG_QTY) BACKSTGQTYSUM,
	BEST_SELLER_IND,
	BACKSTGONLY_NR_IND 
FROM
	dm_vw.fact_backstg_scan A 
		JOIN dm_vw.dim_article B 
		ON A.merchandising_id = B.merchandising_id 
			JOIN dm_vw.dim_calendar C 
			ON A.calendar_id = C.calendar_id 
				JOIN dm_vw.dim_site d 
				ON A.geography_id = d.geography_id 
					LEFT JOIN (	SELECT
									STORE_ONHAND_AVL,
									INTRANSIT_STORE_AVL,
									RDC_NDC_ONHAND_AVL,
									INTRANSIT_RDC_NDC_AVL,
									HC_FSV_ONHAND_AVL 
								FROM
									dm_vw.FACT_ARTICLE_SITE_AVAILABILITY) d 
					ON A.geography_id = d.geography_id AND
					a.calendar_id = d.calendar_id AND
					a.merchandising_id = d.merchandising_id 
WHERE
	(stk_postn_snpsht<>0 OR
	backstg_qty<>0) AND
	C.NATURAL_DATE_DTE>=(current DATE)-7 days AND
	(ART_HIER_LEV5_CODE ='G5-T01'OR ART_HIER_LEV5_CODE = 'G5-T02'OR 
	ART_HIER_LEV5_CODE = 'G5-T06'OR ART_HIER_LEV5_CODE = 'G5-T27'OR 
	ART_HIER_LEV5_CODE = 'G5-T38'OR ART_HIER_LEV5_CODE = 'G5-T41'OR 
	ART_HIER_LEV5_CODE = 'G5-T42'OR ART_HIER_LEV5_CODE = 'G5-T43'OR 
	ART_HIER_LEV5_CODE = 'G5-T49'OR ART_HIER_LEV5_CODE = 'G5-T57'OR 
	ART_HIER_LEV5_CODE = 'G5-T59'OR ART_HIER_LEV5_CODE = 'G5-T33'OR 
	ART_HIER_LEV5_CODE = 'G5-T37'OR ART_HIER_LEV5_CODE = 'G5-T51'OR 
	ART_HIER_LEV5_CODE = 'G5-T52'OR ART_HIER_LEV5_CODE = 'G5-T60'OR 
	ART_HIER_LEV5_CODE = 'G5-T61'OR ART_HIER_LEV5_CODE = 'G5-T81'OR 
	ART_HIER_LEV5_CODE = 'G5-T03'OR ART_HIER_LEV5_CODE = 'G5-T07'OR 
	ART_HIER_LEV5_CODE = 'G5-T08'OR ART_HIER_LEV5_CODE = 'G5-T09'OR 
	ART_HIER_LEV5_CODE = 'G5-T10'OR ART_HIER_LEV5_CODE = 'G5-T11'OR 
	ART_HIER_LEV5_CODE = 'G5-T12'OR ART_HIER_LEV5_CODE = 'G5-T14'OR 
	ART_HIER_LEV5_CODE = 'G5-T15'OR ART_HIER_LEV5_CODE = 'G5-T16'OR 
	ART_HIER_LEV5_CODE = 'G5-T17'OR ART_HIER_LEV5_CODE = 'G5-T19'OR 
	ART_HIER_LEV5_CODE = 'G5-T25'OR ART_HIER_LEV5_CODE = 'G5-T28'OR 
	ART_HIER_LEV5_CODE = 'G5-T30'OR ART_HIER_LEV5_CODE = 'G5-T75'OR 
	ART_HIER_LEV5_CODE = 'G5-T64'OR ART_HIER_LEV5_CODE = 'G5-T71'OR 
	ART_HIER_LEV5_CODE = 'G5-T72'OR ART_HIER_LEV5_CODE = 'G5-T74'OR 
	ART_HIER_LEV5_CODE = 'G5-T76'OR ART_HIER_LEV5_CODE = 'G5-T77'OR 
	ART_HIER_LEV5_CODE = 'G5-T78'OR ART_HIER_LEV5_CODE = 'G5-T86'OR 
	ART_HIER_LEV5_CODE = 'G5-T87'OR ART_HIER_LEV5_CODE = 'G5-T88'OR 
	ART_HIER_LEV5_CODE = 'G5-T92'OR ART_HIER_LEV5_CODE = 'G5-T35'OR 
	ART_HIER_LEV5_CODE = 'G5-T36'OR ART_HIER_LEV5_CODE = 'G5-T47'OR 
	ART_HIER_LEV5_CODE = 'G5-T80'OR ART_HIER_LEV5_CODE = 'G5-T84'OR 
	ART_HIER_LEV5_CODE = 'G5-T18') 
GROUP BY
	NATURAL_DATE_DTE,
	ARTICLE_ID,
	site_id,
	DAY_OF_THE_WEEK,
	PRICE_TYPE,
	BEST_SELLER_IND,
	SITE_NAME,
	ART_HIER_LEV3_NAME,
	BACKSTGONLY_NR_IND

[v-chuncz-msft · Accepted solution]

@darylmc ,

A dedicated forum might be more appropriate.

https://www.sqlbi.com/topics/from-sql-to-dax/

[PattemManohar · Accepted solution]

@darylmc  As @v-chuncz-msft  mentioned it is not the right community for this. As this is for Power BI stuff.

Anyway, as I can see in your query that you are trying to join the fields from d (an alias table) which are not present in the SELECT clause. Even though that table might have those fields but you are selection is restricted to the subset of fields and the join performing outside this will know or can access those fields in SELECT statement alone. So you need to have geography_id,calendar_id and merchandising_id in your select statement of d.

Hope this clarifies your issue.

[v-chuncz-msft · Accepted solution]

@darylmc ,

A dedicated forum might be more appropriate.

https://www.sqlbi.com/topics/from-sql-to-dax/