Need help with combining data from Sheets and MySQL

I have two resources, one is a mysql query and another one is a google sheets query.

The mysql query is as follows:
It's resource name is InventorySummaryMySQL

select 
  SKUID,
  SUM(CASE WHEN (Status = 'FABBED' AND Location = 'FABYARD') THEN Qty END) as QinFab,
  SUM(CASE WHEN (Status = 'FABBED' AND Location = 'GALVYARD') THEN Qty END) as QinGalvYard,
  SUM(CASE WHEN (Status = 'FABBED' AND Location = 'GALVANIZER') THEN Qty END) as QinGalvanizer,
  SUM(CASE WHEN (Status = 'READY' AND Location = 'SHIPPING') THEN Qty END) as QReadyToShip
from Inventory
GROUP BY SKUID

a sheets query as follows that joins to InventorySummaryMySQL

SELECT
  SKUID,
  QinFab,
  QinGalvYard,
  QinGalvanizer,
  QReadyToShip,
  [qstat/On Order] AS OnOrder,
  [qstat/SSIC] AS SSIC,
  [qstat/Shipped] AS Shipped
FROM {{ formatDataAsArray(InventorySummaryMySQL.data)}} as SQLView LEFT JOIN {{ SheetSKU.data }} AS SheetSKU
ON SheetSKU.[🔒 Row ID] = SQLView.SKUID

SheetSKU is just a google sheets resource of a sheet in a google sheet.

This query doesn't work. I can't figure out why. For some reason, when I hover over "data" on

{{ formatDataAsArray(InventorySummaryMySQL.data) }}

The data shows transposed! I can't figure out why this data won't join.

Hey @appiman :wave: Just to clarify -- you are querying a MySQL resource and a Google Sheets resource (using our native integration). You then would like to join this data using a Query JSON with SQL query?

Yes, I am trying to join the two, I think it would work if the results from the SQL query weren’t transposed.

I can’t figure out why it’s being transposed.

@appiman would you mind clarifying what you mean by transposed? I see that you are hovering over InventorySummaryMySQL.data in the screenshot, and you are formatting this data using formatDataAsArray. If you hover over the last two brackets in {{ formatDataAsArray(InventorySummaryMySQL.data) }} do you see the data formatted as you expect? If you format the data as you like outside of this query is it running successfully?

Transposed means that rows turn on columns and the columns turn to rows. Basically rotates the data 90 degrees. Like matrix algebra transposition.

Anyway, I just worked around it. I scrapped the mysql database because I couldn't figure this out and my client was hammering me for results, so the version that I showed no longer exists...

I guess we can close this topic. If I can figure out how !