Join problems - can't display a field

[Jim_Goodman]

My goal: get data from 2 queries to display in 1 table in Retool. It's been a LONG time since I've written any SQL queries, so the problem might be between the chair and the keyboard

Tables that get Queried with scan from Dynamo:
1 - "stores" with: name, storeId, ownerId, integratorId
2 - "integrators" with: name, integratorId

I'd like to display something as attached below, with the POS Name being the value from integrators.name (the name of the POS that is referenced by integratorId in both tables)

This is my current query JSON with SQL statement that queries the 2 queries/tables noted above.

select name, storeId, ownerId, integratorId (I've tried using (*) in my select statement)
from {{  stores.data.Items }} s 
left join {{  integrator.data.Items }} I 
on s.integratorId = i.integratorId
where s.integratorId = "240208002" or s.integratorId = "230428004";

Any help is appreciated.

[MiguelOrtiz]

Hey there @Jim_Goodman, and welcome to the forum!

So there are a couple of things in your query that could be causing issues:

• Table names: In Retool's Query from clause you cannot use two {{ }} blocks like that (unless you disable Prepared statements).
• Aliasing: You're using i. but the alias was defined as uppercase I.

So based on the above I've rewritten your query as follows:

SELECT 
  s.name, 
  s.storeId, 
  s.ownerId, 
  s.integratorId,
  i.name AS integrator_name  -- assuming you want something from integrator
FROM stores s
LEFT JOIN integrators i ON s.integratorId = i.integratorId
WHERE s.integratorId = '240208002' OR s.integratorId = '230428004'

[WidleStudioLLP]

Hey @Jim_Goodman, welcome to the Community forum!

I was testing dummy data in my app and needed to join data from the stores and integrators tables in Retool.

To solve this, I used a JavaScript transformer to join the two arrays based on integratorId. This works well

const store = {{ stores.data }};
const integrator = {{ integrators.data }};

// Only include these integratorIds
const filterIds = ["240208002", "230428004"];

// Join logic
const result = store
  .filter(store => filterIds.includes(store.integratorId))
  .map(store => {
    const integratorss = integrator.find(i => i.integratorId === store.integratorId);
    return {
      "Store Name": store.name,
      "Store ID": store.storeId,
      "Owner ID": store.ownerId,
      "Integrator ID": store.integratorId,
      "POS Name": integratorss ? integratorss.name : "Unknown"
    };
  });

return result;

Here’s the transformer code I used:

[Jim_Goodman]

When I try to use your query @MiguelOrtiz I get an error

[Jim_Goodman]

When I create a stand alone transformer @WidleStudioLLP, I get no output. I'm new to this/transformers, where should I put it so that it runs?

[MiguelOrtiz]

Hey @Jim_Goodman,

Ah, apologies, I didn't realize you were talking about a query JSON with SQL before, so you should definitely ignore my first response.

Making a wild guess here, I think this is not working because of how retool is structuring your stores and integrator data.

Try the below:

select name, storeId, ownerId, integratorId (I've tried using (*) in my select statement)
from {{  formatDataAsArray(stores.data) }} s 
left join {{  formatDataAsArray (integrator.data) }} I 
on s.integratorId = i.integratorId
where s.integratorId = "240208002" or s.integratorId = "230428004";

I'm not sure why you're adding Items after data everytime, and I've never used Dynamo before, so not sure if there is any specific constraint about it that may throwing an error here. If the above doesn't help, would you mind providing a screenshot of how your dynamo's data is being returned (just right click on the query's name and click "View on State".

Thanks!

[WidleStudioLLP]

Hey @Jim_Goodman ,

Review the 'Stores' and 'Integrators' databases and their data. You may use the existing query, such as {{ integrator.data.Items }}, for reference.

const store = {{ stores.data.Items  }};
const integrator = {{ integrators.data.Items  }};

// Only include these integratorIds
const filterIds = ["240208002", "230428004"];

// Join logic
const result = store
  .filter(store => filterIds.includes(store.integratorId))
  .map(store => {
    const integratorss = integrator.find(i => i.integratorId === store.integratorId);
    return {
      "Store Name": store.name,
      "Store ID": store.storeId,
      "Owner ID": store.ownerId,
      "Integrator ID": store.integratorId,
      "POS Name": integratorss ? integratorss.name : "Unknown"
    };
  });

return result;

If this does not work within the transformer, use it as a JavaScript query instead. Run the query, review any errors, and resolve them as needed. You can declare variables like this:

const store =  stores.data.Items  ;
const integrator = {integrators.data.Items ;

[Darren]

Welcome to the community, @Jim_Goodman! Thanks for reaching out.

I'm pretty sure @MiguelOrtiz's recommendation above to use formatDataAsArray is probably what you want! You can even clean up your code a bit by automatically reformatting the output of each query via their built-in transformers.

I hope that helps - let us know if you have any additional questions.