How do I join results from 2 query

Queries and Resources
1

Hi,
I have a table with which I want to join the results from another query.
In the query below project is the table and allTasks is the query which returns a table with columns :project_id & count.
The project table has a column id which I would want to match with project_id column from the allTasks.

select * from project JOIN {{allTasks.data}} as ALL on ALL.project_id=project.id where project.creator_id={{table5.selectedRow.data.id}}

Can you please help me with this. Thanks!

2

Hi @prsahu! I don’t think most databases will support passing in the whole .data object to the server and properly parse it server side. We do have a “Query JSON w/ SQL” type query for this kind of thing though you’ll need to have all of the results that you want to join loaded into their queryName.data properties locally.

If allTasks.data is a query from the same database, you could do this all within a single SQL query without referencing a {{ }} tag for one of the tables to query.

3

Can you give some more direction here? I’m trying to do what would normally be an inner join but trying to figure out how to do that in a single sql query. I have 2 tables - an array of billable employees by name and a table of timesheets that I would like to only return those on the billable list (I know - I don’t have ID’s yet, still prototyping).

I’ve tried a transformer using the .zipWith:

var sqlDataSorted = {{formatDataAsArray(GetHours.data)}}.sort((e1, e2) => e1.id - e2.id)
var apiDataSorted = {{GetBillable.data}}.sort((e1, e2) => e1.id - e2.id)

_.zipWith(sqlDataSorted, apiDataSorted, (sql, api) => Object.assign({}, api, sql))

And I’ve tried a sql query:

select *
from {{GetBillable.data}} as billable
join {{GetHours.data}} as timesheets
on billable.Full_Name = timesheets.Employee

Thoughts?

4

@Bill_Waring the Query JSON With SQL syntax can be a bit hairy. One issue is capital letters - you’ll need to string quote those column names. Are you getting any errors you can share?

5

I think I figured it out. Yes, it was syntax.

Thanks!

1 Like