How do I have a select query inside a where clause?

[cesc34]

Hello,

I am trying to implement the below query in Knime using nodes.

Select * from TableA where column1 in (Select column2 from TableB)

The query “Select column2 from TableB” needs to be in a node of its own. So how do I use the output of the node in a where clause

[takbb]

Hi @cesc34, and welcome to the KNIME community.

If the “TableB” data is already in a KNIME table, but you wish to use it in the SQL query, your options are to either manually turn the “Column2” values into a comma delimited string, held in a flow variable, which can then be used in the select statement against TableA, or an alternative is to use the DB Looping node.

Take a look at this thread, where both these approaches are referenced:

Extract data from db using value from other db

Hi people, Database B, Table A: User_ID 001 002 003 004 005 Table A is the result I got from Database B. I want to use value in column User_ID to retrieve data from Database A using DB Table Selector (it’s connected to DB Connector). The data would be too large if I just extract everything, so I will need to specify which value to retrieve only in Database A. For now, I do the work manually like custom the query in the DB Table Selector node myself: SELECT * FROM #table# WHERE User_ID …

If the data in TableA is not massive, you could also simply return all the rows from TableA using a DB Query Reader and then use a Joiner node to join column1 to the column2 data that has been returned from TableB, but whilst less efficient, this approach can be perfectly valid, for a “quick win”.

[AlexanderFillbrunn]

Hi,
You can also use the DB Query Extractor node to turn the Select column2 from TableB into a flow variable. Then do the same with the Select * from TableA (both queries can be generated using KNIME DB nodes). Now you have two parts of your query as strings and you can use a String Manipulation node to connect them with the IN operator. Then you can use the DB Query Injector to turn the new query into a DB Data port. The whole logic can easily be wrapped into a component for reusability.
Kind regards,
Alexander

[cesc34]

The data in TableA is very large, so the joiner node is taking a long time and that’s the reason I am trying to explore different options. Let me check the thread and see if that helps