cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darylmc Frequent Visitor
Frequent Visitor

SQL server - joins on multiple columns

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: SQL server - joins on multiple columns

@darylmc ,

 

A dedicated forum might be more appropriate.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User
Super User

Re: SQL server - joins on multiple columns

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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





2 REPLIES 2
Community Support Team
Community Support Team

Re: SQL server - joins on multiple columns

@darylmc ,

 

A dedicated forum might be more appropriate.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User
Super User

Re: SQL server - joins on multiple columns

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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !