Is it possible to do a cross join (or a workaround)

[asesores]

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!

Thank you!

[Vitalikssssss]

Hi @asesores,

That is very interesting question.

I come up with a workaround which would allow to create a cross join between 2 tables in Caspio.

Basically, you would need to add an additional field in both tables and specify the identical values for all records. 

Table #1

 

Table #2

Create a View with Full Outer Join by the Join Field:

Here is a result which you shall have:

 

Hope this helps.

Regards,

vitalikssssss

 

[asesores]

@Vitalikssssss thank you!! This is perfect

[DaveS]

@Vitalikssssss - You are a hero - this is a great solution for something that I wrestled with for WAY too long.  Thank you and thanks, @asesores for posting the question!