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

[ArmyGrooSMH]

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

[Hammad_Rashid]

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!