how to make 'where not exist' in mysql syntax?

[evgeniystuchalk]

Hello everyone! I'm trying to load data from mysql table with this condition:

FROM `amo.demo`.contacts where not exists (tsKey,`i_companyID`&'|'&`s_responsibleUser`&'|'&`i_contactID`&'|'&null());

And keep getting message about wrong syntax for mysql. What syntax is correct in this situation?

[Gysbert_Wassenaar]

No idea. You'd have to ask someone specialized in the MySQL SQL dialect. The easy way out is to use a preceding load:

MyTable

LOAD * WHERE NOT Exists( ....etc... ) ;     // preceding load

SELECT * FROM `amo.demo`.contacts;     //  load data from mysql table

[oknotsen]

What is it you are actually trying to exclude?

What you could do it add an Inline table to your script with just 1 field that contains a list of the values you do not want to include. Next, add "where not exists(fieldInInlineTable, fieldInAmo.DemoTable)". Drop the Inline table after that.

But based on what I think to understand from your example is that you first want to make some combination of field and exclude that combination. Maybe you can give us a more clear example?

[swuehl]

The correct syntax usually requires that you use a subquery in exists(....), see for example

MySQL :: MySQL 5.7 Reference Manual :: 13.2.10.6 Subqueries with EXISTS or NOT EXISTS‌

But the correct syntax might not solve your requirements alone, i.e. you should be aware that exists() in QV and exists() in SQL are fundamental different besides their syntax.

Exists() in QV is checking if a field value loaded so far (up to the previous ouput table record, but including all previous field values loaded) is identical with in field value or an expression evaluated with the current input record.

So a key question () looking at your sample line would be, if the tsKey is present in your output table of this table load and if you would need to handle multiple occurences of composite key.

If in doubt or there is no real need to push load onto the DB server, go with Gysbert's suggested solution.

Regards,

Stefan

[evgeniystuchalk · Accepted solution]

My dumb, but working solution:

Load table from MySQL as temporary table, after that load data via Tesident command with requared limitations.