I have two REST apis (user DBs) bigDB and smallDB. bigDB is bigger than smallDB because not all users that are in bigDB are also in smallDB. i want to show a table with all users of bigDB and add data from smallDB when available. the shared identifyer is email.
I think this can be done with a transformer similar to the example on the docs (zipWith) page but I fail to get it working.
Hey curesw, I think a Query JSON with SQL query might be a better option to join the data from your two queries.
https://docs.retool.com/docs/querying-via-sql
Here's an example of a Query JSON with SQL query where I join a Mongo query with a Postgres query (on matching 'blarg' and 'name' columns, respectively)
One thing to note! Mongo queries return their .data as an array of objects (perfect for the Query JSON with SQL query type). Others (like Postgres) return an object of arrays, so we need to use the formatDataAsArray method to transform the postgresQuery.data array.
I hope this can work for your use case 
Great, that helps. Now I have a stupid SQL follow-up question:
Now I join two REST Apis that each contains a field id. In my table I will need both. But I get only one with currentRow.id.
I use
select * from {{getUsersAa.data}} as aa join {{getUsersBb.data}} as bb on aa.email = bb.email
and get a good result, except that I cannot access aa.id (or at least don't know how to reference it later)
Hey @curesw, Not a stupid question at all You might need to alias the id columns (as well as any other columns that have the same name) so that they aren't both 'id'. Here's an example of what that could look like:
I hope that helps!
Thank you, but do I really have to explicitly list each field of a and b instead of using the wildcard * ? Is there no combination like "a.id as a_id, b.id as b_id, * from ..."
Hey @curesw, no you don't need to explicitly list each field of a and b! You can use the wildcard but you will need to apply the wildcard to each aliased dataset in this case.
