Hi, I need to perform a cross join between two tables and I'm looking for any possible way or workaround.
Context: I have one user table and one opportunity table, I want to do a cross join between the two tables so I can get one table that contains every user and opportunity pair, then create a calculated column to define a "compatibility score" for each pair. I know this will be a large table and cross joins are generally ill-advised, but I believe this is a valid use case for one.
Brainstorming: Cross joins are not an option when creating a view, but not sure if there is some workaround for this? From research it doesn't look like there are any ODBC connectors that could be used by some sort of SQL query tool to query the tables with a cross join and then load the data back into Caspio. It would probably be possible to use the API to pull data out then use another tool to perform the cross join and load the data back in, but this seems a bit clunky and inefficient.
Don't know if I've missed anything obvious but any ideas, suggestions, or feedback would be great!