Join multiple tables - Left join

Question

I'm trying to convert existing SQL query into alteryx workflow. I am having three tables FinalData, Client and RSDBdata and below is the SQL query, please anyone help me to create workflow for the below query.

SELECT FinalData.*, Client.[ID Name], Client.Type, Client.Collateral, Client.[Payment No] AS Payment, IIf([Group Relationship]="Y","Y","N") AS [Group Rel], RSDBdata.Business, RSDBdata.Manager, RSDBdata.business_type_2 AS [BusinessType 2], 
FROM (FinalData LEFT JOIN RSDBdata ON (FinalData.code = RSDBdata.[Sort Code1]) AND (FinalData.refer_ID = RSDBdata.refer_ID_no)) LEFT JOIN Client ON (FinalData.refer_ID = Client.ReferID) AND (FinalData.code = Client.[Sort code]) AND (FinalData.ID_number = Client.CustID)
WHERE (((FinalData.ID_number) Is Not Null));

troy_mech · Answer

Thank you for your response.

The query I shared is created in MS Access and the data is combination of static table. I'. I'm trying to build workflow for the procedure designed in MS Access DB. There are no data retrieved from SQL database, the tables are static one.

atcodedog05 · Answer

Hi @troy_mech

I agree with @mbarone idea of giving this query as a custom query while inputting the data it will give you the desired output easily.

If you want to create a workflow for that SQL query syntax. Then we need sample data because there is lot more than joins thats happening in the query.

mbarone · Answer

Hi @troy_mech ,

Not sure what you're going for here . . . but you can actually use Alteryx to connect directly to your data source and run that query against it, and have the results come back into Alteryx.  You can use this using the In-Database tools, or even a normal Input Data tool.

But, if you're simply asking how to do a left join using Alteryx tools:

If joining two tables, you'd use the Join tool, and union the "L" output node, and the "J" output node.

If joining more than two tables, you'd use a Join Multiple tool, and place a filter after after it, and in the filter look for a non-null key.