Jump to content
  • 0
Sign in to follow this  
asesores

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

Question

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!

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 1

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

 image.png

Table #2

image.png

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

image.png

Here is a result which you shall have:

image.png

 

Hope this helps.

Regards,

vitalikssssss

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...