Thought this might help, if you aren’t coming from an SQL background then it will probably confuse!
Two Tables, not linked (we can do that another day). Let’s not think about duplicate values either.
Table A
Name
Parrot, Banana, Monkey, Apple
Table B
Name
Ruby, Banana, Diamond, Apple
INNER JOIN
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name
In Bubble this is achieved via an INTERSECT of two searches.
FULL OUTER JOIN
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
Note that in SQL you will get TableA or TableB null names back even if there is no match - this does not happen in Bubble.
In Bubble this is achieved via a MERGE of two searches.
FULL OUTER JOIN WITH EXCLUDES
So all the unique values from Table A and Table B.
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null
Tricky in Bubble, but not impossible…
Left Side…
Right Side…
And merge.
LEFT OUTER JOIN
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
In Bubble you can achieve this by doing the Join in the Repeating Group.
So get the LEFT OUTER
Then JOIN where it exists.
Obviously a RIGHT OUTER JOIN is very similar 
Thanks! This is very useful to have on a cheatsheet
Please add this post to the mandatory new bubbler reading list.
very very useful. Thank you!
Just what I needed to move forward with the cause&effect-app I’m working on.
Thank you so much for this excellent explanation @NigelG!
If type of context is thing, not text?
tks
So is this possible in bubble? I know the data structure is, I have that setup.
The display is where I am stumped.
It won’t work in Bubble because Bubble knows that …
t.usr_unique_id = m.usr_unique_id cannot return a single value of a meeting.
Probably what SQL will do is return you the first one it comes across. Which may happen to be right (and you can do that in Bubble with :firstitem) but it doesn’t make a lot of sense.
“Find me all the tests I passed in the last three weeks, and tell me the test date…and the meeting date and location of a Meeting” ? What meeting ?
My guess would be that there is a relationship between meetings and tests that is not on there. ?
Thanks for the response @NigelG
Yes, there is a relation between meetings and test through usr_uniqueid.
Take the case of: Gives me all the Meetings any user has had for a Test that was a Pass=Yes. My application will then plot the Meeting’s location on a Map. I will display the Meeting Location and Test Date in the table.
Should this be possible in Bubble?
Is the current structure making it harder for Bubble?
Do I need to re-think the data structure
Would love to hear any recommendations.
Thanks again @NigelG
User to Meeting is 1: Many
User to Test is 1: Many
If usr_uniqueid is unique per user, then Meeting <> Test is still many to many.
Or does the data model indicate there is a one to one between Meeting and Test based on some unique value ?
Or …can a meeting be for many tests ? Or can you do many tests at a meeting ?
Absolutely right… Meeting to Test is a many to many. There is no direct relation i.e. a set of Meetings for a particular Test.
Well, I still don’t know how this works in SQL (as there is no specific Meeting or Location for a test) so can’t really say how to do it in Bubble.
I have created a sample. You can take the sample table script and sample query from:
https://drive.google.com/drive/folders/1d8NvCnaY6tC8WTrnvhJpB2zmCK4KbwYc?usp=sharing
If you’d like to see it, please go to sqltest.net, choose Oracle 11g database on the top right corner,
Paste the script (from the above link) in the left pane and the query in the right pane. Click Execute SQL at the bottom to see the result.
Thanks again @NigelG
Unless there is another/better alternative, looks like in Bubble, I may need to break down the query, i.e. perform the inner query first, convert it to a list and use that in the outer query (filtered: Advanced ).
I was, in crude ways, able to achieve the result, perhaps inefficiently.
