Query Best Practice

Queries and Resources
,
1

Hi Everyone:

I have what is probably a remedial question, but I am struggling to come up with the best way to implement it. I am working on a workflow that will get the latest shipped sales orders, and send an email. The database I am querying has a Sales Order -> Line Items relationship.

Simplified Example:
SalesOrderTable

SalesOrderID Customer SoldDate ShipDate
1 Customer1 12/7/22 12/7/22
2 Customer1 12/7/22 12/7/22
3 Customer2 12/7/22 12/7/22

Lines on the sales orders

LineItemID LineNumber SalesOrderID Product Price
1 1 1 Eggs $1.00
2 2 1 Milk $1.00
3 1 2 Bread $1.00
4 2 2 Eggs $1.00
5 3 2 Milk $1.00
6 1 3 Eggs $1.00

I am unsure the best way to join the lines to the sales orders. Obviously simply joining the data isn't really what I want, I don't think. Meaning

SELECT * FROM SalesOrder
JOIN lines ON SalesOrder.SalesOrderID = Lines.SalesOrderID
WHERE SalesOrderID = 2

Would produce something like

SalesOrderID Customer SoldDate ShipDate LineItemID LineNumber SalesOrderID Product Price
2 Customer2 12/7/22 12/7/22 3 1 2 Bread $1.00
2 Customer2 12/7/22 12/7/22 4 2 2 Eggs $1.00
2 Customer2 12/7/22 12/7/22 5 3 2 Milk $1.00

Ultimately I am trying to feed this data into Carbone.io from Retool Workflow to make a PDF sales order using workflow, which I believe needs to be JSON similar to below

"SalesOrderID": 2,
"Customer": "Customer2",
"SoldDate": "12/7/22",
"LineItems": {ArrayOfLineItems}

I've tried selecting all Sales orders and then using a workflow loop to iterate over each one, selecting all the line items for each line in the sales order. This gives me the line items for each sales order, but I can't seem to figure out how to get the line items and sales order in the same JSON.

Sorry for the long post, but appreciate any feedback or suggestions on the best way to attempt this!

2

Hey @darenhunter!

The exact syntax for this may vary depending on what type of SQL resource you're using. In general, though, you might try first running an aggregation on your line items table, grouping by SalesOrderID and then joining that result with your SalesOrderTable.

Let me know if that's helpful! Otherwise if you share the SQL type you're using I'll be happy to look into more specific syntax :slightly_smiling_face:

3

I think I found a solution by utilizing query json. I am utilizing the array() function like array({key:database value, key2:databasevalue2}) and grouping by the non-unique values.

1 Like
4

Which after reading your response, is I think more or less what you are suggesting.

5

Array · AlaSQL/alasql Wiki · GitHub this is the documentation I found to supplement my answer in case it helps anyone!