How to join two different queries from different resources

Hi i have 2 different queries that pull data from 2 different resources:


SELECT    uri_identity
          , first_name
          , last_name
FROM      mem_client_card
WHERE     business_id = {{[0]}};

and GetAllForms_EU:

select id, client_id, business_id
from consultation_form
where business_id = {{[0]}}

how do I pull them together to construct what I need which is to generate a URL of data included in the GetAllForms_EU query and pull the client name associated with the form from GetAllClients_EU query?

I was trying something like a js script but keep getting errors of no data when I run it - even though if I run the 2 queries on their own data is there.
Is there perhaps a simpler way to join data from 2 tables from 2 different resources so I can do everything I need from within a simple SQL query?

My js script so far is:

async function fetchData() {
    try {
        // Execute both queries
        const [clientsResponse, formsResponse] = await Promise.all([

        // Check if both queries returned data
        if (!clientsResponse || !formsResponse) {
            console.error('One or both queries did not return data');
            return [];

        // Extract data from the query results
        const clientsData = clientsResponse;
        const formsData = formsResponse;

        // Initialize an array to store the constructed URLs
        const urlData = [];

        // Iterate over each form record to construct URLs
        formsData.forEach(form => {
            // Find the corresponding client information
            const client = clientsData.find(client => client.uri_identity === form.client_id);

            if (client) {
                // Extract required data for URL construction
                const business_id = form.business_id;
                const client_id = form.client_id;
                const form_id =;
                const client_name = `${client.first_name}_${client.last_name}`;

                // Construct the URL using the extracted data
                const constructedURL = `${business_id}/clients/${client_id}/consultations/forms/${form_id},${client_name}`;

                // Add the constructed URL to the array
                urlData.push({ URL: constructedURL });

        // Return the array containing the constructed URLs
        return urlData;
    } catch (error) {
        console.error('An error occurred:', error);
        return [];

// Execute the fetchData function
const urlData = await fetchData();

What I basically need to achieve - if I was doing it outside of retool is simply a query like this that uses a simple join:

         || forms.business_id
         || '/clients/'
         || forms.client_id
         || '/consultations/forms/'
         || )        AS formUrl
       , ( mcc.first_name
           || '_'
           || mcc.last_name ) AS ClientName
FROM   forms_eu.consultation_form AS forms
       JOIN memento_master_eu.mem_client_card AS mcc
         ON forms.client_id = mcc.uri_identity
WHERE  forms.business_id = 'blablablablablabla'
       AND forms.status = 'COMPLETED'
ORDER  BY clientname

You might be glad to know that you can do that SQL Join inside Retool using a query, might be what you need?

Hey @dcartlidge - apologies for my ignorance here - but how does json help me here - the data I get off both those queries (which I need to join) is from a specific resource and usually I output it to a table or comes down as a csv (which is what I need as my end result in this case)

If I create a query and use the resource: 'Query JSON with SQL' I imagine I need to reference a different data source in my from - is this something I need to create from my SQL queries using a script - im sorry if im way off the mark here - I've looked at the docs but don't understand how I implement this suggestion in my case

@Bailey Something like this

Here query6 and query 7 are different resources

Ahh right - gotcha - thanks very much will give that a stab :+1: