Check if records exists in a table of a different database / LEFT JOIN with 2 databases

I got stuck with the following problem:

I have 2 databases. MS SQL an a local network server and MySQL on a remote server.

I’m synchronizing 2 tables. Now I want to check if records of the local table exists in the remote table.

On the same database I would do it with a LEFT JOIN with the condition of NULL.

How would I do this with 2 tables of 2 different databases?

This is what I have:

The only way to do that is using 2 different queries to both databases, you can’t do a query to 2 databases.

I know, this is what I have:

or do you mean something else?

The first query on my MS SQL database is:

SELECT * FROM *.*Kontakt WHERE FORMAT(ErstelltAm, 'yyyy-MM-dd') = CAST(GETDATE() AS DATE)

the second query is like in the screenshot above and I get this error:

{"status":"500","code":"ER_PARSE_ERROR","message":"SELECT * FROM *_*Kontakt WHERE (Date(ErstelltAm) = CURDATE()) AND KontaktId NOT IN '3627C28C-898A-4768-AD09-5614F3D4318C' - ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''3627C28C-898A-4768-AD09-5614F3D4318C'' at line 1","stack":"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''3627C28C-898A-4768-AD09-5614F3D4318C'' at line 1
    at Query.Sequence._packetToError (/*/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
	at Query.ErrorPacket (/*/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
	at Protocol._parsePacket (/*/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/*/node_modules/mysql/lib/protocol/Parser.js:433:10)\n    at Parser.write (/*/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/*/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at TLSSocket.ondata (internal/streams/readable.js:719:22)
    at TLSSocket.emit (events.js:315:20)
    at addChunk (internal/streams/readable.js:309:12)\n    at readableAddChunk (internal/streams/readable.js:284:9)
    at Protocol._enqueue (/*/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/*/node_modules/mysql/lib/Connection.js:198:25)
    at /*/node_modules/knex/lib/dialects/mysql/index.js:132:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/*/node_modules/knex/lib/dialects/mysql/index.js:126:12)
    at executeQuery (/*/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
    at Client_MySQL.query (/*/node_modules/knex/lib/client.js:144:12)
    at Runner.query (/*/node_modules/knex/lib/execution/runner.js:130:36)
    at ensureConnectionCallback (/*/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)
    at Runner.ensureConnection (/*/node_modules/knex/lib/execution/runner.js:272:20)"}

The SQL syntax is incorrect. You have

SELECT * FROM *_*Kontakt WHERE (Date(ErstelltAm) = CURDATE()) AND KontaktId NOT IN '3627C28C-898A-4768-AD09-5614F3D4318C'

You should use <> instead of NOT IN if you check a single id or correct the syntax to NOT IN (:P1).

Thanks for your tip. Sadly I don’t get the desired results.

Would it be possible to get a LEFT JOIN Server Action in Data Transformation similar to the JOIN Action?

@mebeingken did you already have a similar task to do and give me a hint? I remember that you also worked with 2 databases.

The ability to join results was added to wappler after I needed to do this, but I documented it here:

You just use values from the first lookup inside the second lookup.

1 Like

I think that this is the way I did the query. My problem is that I have to check what records are missing in the remote table. How would you do the following query with your solution?

	qKontaktTodayRemote.KontaktId AS RemoteId 
	LEFT JOIN qKontaktTodayRemote ON qKontaktTodayLocal.KontaktId = qKontaktTodayRemote.KontaktId 

You are selecting from qKontaktTodayLocal in the SELECT clause, but your FROM has qKontaktToday.

Shouldn’t those match?

You are right :grinning:, I edited the query and forgot to change the qKontaktToday to qKontaktTodayLocal.
My example was like I would do it on the same database. Should this also work with your setup?

I don’t understand your question.

sorry about that. I think my problem is that I don’t get at what step of my setup (post 1 and 3) I would use my custom query to make it work.

A custom query is not a requirement when using two data sources, you can do regular queries inside the repeat. The general concept is that you do your first query, then repeat over those record to get corresponding values from the 2nd database. If you have a lot of records, it might not be the best approach as you are doing a query for each record in the repeat.

That is exactly my problem and that’s why I would like to simulate a LEFT JOIN but can’t understand the way this would work with 2 databases. I have about 560 records a day and with a LEFT JOIN I would get the missing records within a second. If I understand it correctly the new JOIN module does only do an INNER JOIN.
The reason why I used a custom query is that I need to check all records of today: WHERE FORMAT(ErstelltAm, 'yyyy-MM-dd') = CAST(GETDATE() AS DATE).
This is not possible with a regular query.

If you are trying to use a single query to retrieve records across two database connects, you can stop trying that approach. :slightly_smiling_face:

If the Join action doesn’t work for you, then you could make a custom formatter.

1 Like

I solved the problem by using Join from the Data Transformations. I wasn’t aware that it uses a LEFT JOIN as standard.

It would be great if I could set a condition in the Join properties. This should speed up my current solution with *.length() == 0.