Hi Guys,
Trying to do a NOT IN query. Any advice on how to do this.
The query preview is misleading (wrong) when using IN or NOT IN. You might assume that a comma-separated list of number will work according to the preview:
… but this is not the query which is actually executed. You need to convert the string to an array, eg:
The same will apply of course if you’re using variables in the query.
1 Like
Well it is not wrong - “in” and “not in” always expect the provided value to be an array …
Can you explain what goes wrong, or what’s not working properly? What are you checking against what?
It is the preview which is wrong. Using my example, the query shown is:
SELECT * FROM items WHERE item_id NOT IN (1,2,3) - which works as expected
However this is not the query which is executed. This is what is executed:
SELECT * FROM items WHERE item_id NOT IN (‘1,2,3’) - which is not the same query (and is unlikely to give the expected results)
Ah, are you trying to do sometime like:
SELECT [field] FROM [table] where [field] NOT IN SELECT [field] FROM [anothertable]
rather than using the SQL IN() function on an array?
Your value for the IN/NOT in should be:
{{delivery.join(",", "COLUMN_NAME_HERE").split(",")}}
This way the second query will return the values which are IN or NOT IN the column from the first query.
1 Like
if i am following you correctly the following join will achieve what you want to do.
SELECT [field list] from [tablea] LEFT JOIN [tableb] ON KEY [tablea].[keyfield] = [tableb].[keyyfield] WHERE [tablea].[keyfield] IS NULL
This will give all entries in tablea which are not in tableb
Here is a great graphic detailing join types.
2 Likes
That should do the job as well.