Query record without a relationship

Certified Associate Developer

Hi guys.

I'm new to complex queries with records and I've got a question about how to execute a query based on records when there is no key based relationship between two tables.

Imagine a situation where there are two tables

1. Executed jobs table

Jobs      
Id Summary Employee Hours Timestamp
1 Pipe change johnb 3 10/12/2022 12:34
2 Cleaning andyw 5 08/12/2022 10:22
3 Installation johnb 2 03/12/2022 09:12
4 Destruction johnb 4 02/12/2022 18:34

2. Hourly rates of employees

Rates        
Id Employee From To HourlyRate
1 johnb 01/02/2021 30/11/2021 12
2 andyw 01/03/2021 31/01/2022 8
3 johnb 01/12/2022   14
4 andyw 01/02/2022 31/12/2022 11
5 andyw 01/01/2023   14

In result I need to list executed jobs with their cost. But the join between these tables is based on employee name and timestamp of job fitting into rate of employee at job execution time. Using SQL I could perform a join on multiple fields, something like

SELECT jobs.id, jobs.summary, jobs.employee, jobs.timestamp, (jobs.hours * rates.HourlyRate) as jobCost
FROM jobs
LEFT JOIN rates ON (jobs.employee = rates.employee AND jobs.timestamp BETWEEN rates.from and rates.to)

How to do that in the queryRecordType world?

I could do a query, and then a loop and for each job record execute a calculation with retrieving rate information. But would result in multiple SQL statements, and I'm guessing there are better ways for it, right?

And just for the sake of it, it's not that I have a small table of 4 jobs and 5 rates for 2 employees Slight smile This is just an example representation of an obviously larger set of data.

Help will be much appreciated Slight smile

  Discussion posts and replies are publicly visible