Join multiple tables - Left join
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @troy_mech
You need to create a data connection first so that you will be able use this SQL Query. If it's SQL Server you can use this to get it done https://help.alteryx.com/current/designer/connect-sql-server-database
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry, I should have explained clearly. I wanted to create a workflow for that SQL query syntax.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
