Cross Join with QueryBuilder (NOT cross database)

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Cross Join with QueryBuilder (NOT cross database)

Postby steve1376656734 » Thu Jun 04, 2020 2:39 pm

Hi,

In SQL I can type the following:
Code: Select all
SELECT *
FROM T1
CROSS JOIN T2

This will give me the cartesian product of the two tables. E.g.
Code: Select all
CREATE TABLE T1 (label CHAR(1) PRIMARY KEY);

CREATE TABLE T2 (score INT PRIMARY KEY);

INSERT INTO T1 (label)
VALUES
   ('A'),
   ('B');

INSERT INTO T2 (score)
VALUES
   (1),
   (2),
   (3);


Running the above SQL query on these tables would give a result set that contains:
Code: Select all
label | score
-------+-------
A     |     1
B     |     1
A     |     2
B     |     2
A     |     3
B     |     3


How can I acheive the same result using QueryBuilder?

Thanks
Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: Cross Join with QueryBuilder (NOT cross database)

Postby sbutler » Fri Jun 05, 2020 4:32 pm

In SQL, a CROSS JOIN would be the same as a JOIN on 1=1, so in theory, you could do something similar with the query builder. Or any criteria which will be true for all combined rows. Like join on the PK column > -1 or something like that.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Cross Join with QueryBuilder (NOT cross database)

Postby steve1376656734 » Fri Jun 05, 2020 7:35 pm

Doh! - I was so focused on the CROSS JOIN syntax (and lack of it in Servoy), I didn't stop to think what the underlying logic would be!

Thanks Scott
Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK


Return to Programming with Servoy

Who is online

Users browsing this forum: Google [Bot] and 10 guests

cron