SQL Join (Query Builder vs SQL), and what am I missing?RSLVD
Posted: Tue Jun 26, 2018 5:43 pm
Hey all,
I'm attempting to get an address from a UUID in another table.
It was part of a pretty complex query in Query Builder, and I use this for migration to other databases without much ado.
I do get a foundset but I do not really get the join, but I should at least have at least the job_number and the city from the matching record in the addresses table.
EDIT: Is this because foundsets are ONLY single tables with relations pulling the other data from tables as needed? Whereas SQL queries and Datasets are not strictly adhering to a single table?
**works SQL Editor Servoy 7.4.8**
select * from jobs
join addresses
on jobs.ship_to=addresses.address_id
where jobs.job_id = 'B888886D-ED38-4A5E-964A-350314586990'
**doesn't work**
Thanks!
--Joe.
I'm attempting to get an address from a UUID in another table.
It was part of a pretty complex query in Query Builder, and I use this for migration to other databases without much ado.
I do get a foundset but I do not really get the join, but I should at least have at least the job_number and the city from the matching record in the addresses table.
EDIT: Is this because foundsets are ONLY single tables with relations pulling the other data from tables as needed? Whereas SQL queries and Datasets are not strictly adhering to a single table?
**works SQL Editor Servoy 7.4.8**
select * from jobs
join addresses
on jobs.ship_to=addresses.address_id
where jobs.job_id = 'B888886D-ED38-4A5E-964A-350314586990'
**doesn't work**
- Code: Select all
/** @type {QBSelect<db:/stsservoy/jobs>} */
var q = databaseManager.createSelect('db:/stsservoy/jobs');
q.where.add(q.columns.job_id.eq(jobId)); // jobId is a UUID which exists
/** @type {QBJoin<db:/stsservoy/addresses>} */
var r = q.joins.add('db:/stsservoy/addresses');
r.on.add(r.columns.address_id.eq(q.columns.ship_to));
q.result.add(q.columns.job_number);//even if these are left out still get a foundset, but no join with corresponding entry
q.result.add(r.columns.city);
var Q = databaseManager.getFoundSet(q);
application.output('size '+Q.getSize());
var rc = Q.getRecord(1);
application.output('city '+rc.city+' '+rc);
Thanks!
--Joe.