Bring your best ideas to the AI Use Case Contest! Enter to win 40 hours of expert engineering support and bring your vision to life using the powerful combination of Alteryx + AI. Learn more now, or go straight to the submission form.
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Limit the data being requested from a SQL table based on the output of another table

ArmyGrooSMH
5 - Atom

Hello,

 

I have an input query (InputQuery1) of one of my tables - 

SELECT * FROM "database"."hsi"."keygroupdata105" where kg203 is not null

 

InputQuery1 returns 100k rows of data with a unique field of itemnum.

 

I would like to use this subset of data to import data from another table which has 100 million+ rows.  

SELECT * FROM "database"."hsi"."itemdata" 

 

I only want to bring in the data from itemdata where it matches from the first query.  I get this with a join but it still queries the full dataset before the join.

 

How do I limit the amount of data in my initial request from "itemdata" to only be that which matches data from my first?

 

I think I need an inner join of my first dataset vs second dataset but I do not see a way to explicitly say DO NOT give me any non-matching data.

 

But that still fills the first table before the join.  I need to somehow tell the input data to take the results from my first query and then look for the rest of the data.

 

Trying Dynamic input tool to the right of my output from the first table using itemnum as the first for read list of data sources.

I get the following error -

Error: Dynamic Input (8): Error opening "SELECT * FROM "91761173"": No Columns Returned.

 

 

Thank you,

 

ArmyGroo

1 REPLY 1
Hammad_Rashid
11 - Bolide

Try this:

 

Input Data Tool for “keygroupdata105”:

  1. Use an Input Data tool to bring in the results of your first query (“keygroupdata105”).
  2. Ensure that you only select the necessary fields, especially the “itemnum” field.

Input Data Tool for “itemdata”:

  1. Use another Input Data tool to bring in the “itemdata” table.
  2. This time, you don’t need to filter anything since you’ll be doing the filtering in the next step.

Join Tool:

  1. Connect the Output anchor of the “keygroupdata105” Input Data tool to the Input anchor of a Join tool.
  2. Connect the Output anchor of the “itemdata” Input Data tool to the Join tool as well.
  3. Configure the Join tool to perform an inner join on the “itemnum” field.
  4. This will ensure that only the data from “itemdata” that matches the “itemnum” field in “keygroupdata105” is brought in.

I hope this helps!

Labels
Top Solution Authors