NOT IN MySQL Query In Server Action

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:

image

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?

What if the list is dynamic as in, the results of another database query. Check my screen shot

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.

joins

2 Likes

That should do the job as well.