Replicating Multiple join (Query) in Alteryx Workflow

[tjamal1]

I am trying to replicate a query Alteryx which have multiple joins.

Whats to correct approach to replicate query in Alteryx Work Flow ? 

SELECT Distinct [StateProvinceName] as State, pc.[EnglishProductCategoryName], [OrderQuantity]
FROM [AdventureWorksDW2012].[dbo].[DimGeography] as g

INNER JOIN [AdventureWorksDW2012].[dbo].[FactResellerSales] as rs
ON rs.SalesTerritoryKey = g.SalesTerritoryKey

INNER JOIN
[AdventureWorksDW2012].[dbo].[DimProduct] as p
ON p.ProductKey = rs.ProductKey

INNER JOIN [AdventureWorksDW2012].[dbo].[DimProductSubcategory]as sc
ON sc.ProductSubcategoryKey = p.ProductSubcategoryKey

INNER JOIN [AdventureWorksDW2012].[dbo].[DimProductCategory] as pc
ON pc.ProductCategoryKey = sc.ProductCategoryKey

I have tried this

[jamielaird]

Hi @tjamal1 

Conceptually that approach seems fine to me. If you are making multiple inner joins the correct process would be to chain together multiple Join tools and use the Join output from each as an input for the next.

[tjamal1]

Thanks for the reply 

So that means this query requires total of 3 join tool in workflow ?

INNER JOIN [AdventureWorksDW2012].[dbo].[FactResellerSales] as rs 
ON rs.SalesTerritoryKey = g.SalesTerritoryKey

INNER JOIN 
[AdventureWorksDW2012].[dbo].[DimProduct] as p 
ON p.ProductKey = rs.ProductKey

[danilang · Accepted solution]

Hi @tjamal1 

For a straight chain of inner joins linking N tables, you need N-1 Joins, one for every Join line in the original SQL statement

Above is your original query.  For your last query linking just the first 3 tables, you only need 2 Join tools

Dan

[tjamal1]

Thanks,

Have a good Day !