Forum Academy Marketplace Showcase Pricing Features

SQL Joins Vs Bubble

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

image

In Bubble this is achieved via an INTERSECT of two searches.

image

image

35 Likes

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.

image

In Bubble this is achieved via a MERGE of two searches.

image

image

15 Likes

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

image

Tricky in Bubble, but not impossible…

image

Left Side…

Right Side…

And merge.

image

18 Likes

LEFT OUTER JOIN

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

image

In Bubble you can achieve this by doing the Join in the Repeating Group.

So get the LEFT OUTER

image

Then JOIN where it exists.

image

Obviously a RIGHT OUTER JOIN is very similar :slight_smile:

26 Likes

Thanks! This is very useful to have on a cheatsheet :slight_smile:

1 Like

Please add this post to the mandatory new bubbler reading list. :slight_smile:

5 Likes

very very useful. Thank you!

Excellent! Thanks so much for doing this. Very helpful. And I agree with @mebeingken’s comment.

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!

1 Like

If type of context is thing, not text?

tks

1 Like

So is this possible in bubble? I know the data structure is, I have that setup.
The display is where I am stumped.

@NigelG

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.