How to join 2 tables

[junior.cfaj]

Hey guys,

I’m creating an app to find any product in many stores and show their prices. I don’t know if I’ve done right because I had to create 3 tables. First table is store, second is product and third is price-product with columns store, product and price.

In the product page I want to show all stores with that product and show their prices. This is the problem, I don’t know how to do it…

Could anyone help me?

Thanks!

[umiumansa]

Hi,

Record Data
The basic use of data tables in Bubble is my referencing items between tables. To do that, say you start at the top with your “Store” object, which you create in the Database.

This will give you a basic store:

You can Create a new field to add a name etc.

Second, you will create another type of object called “Item” and you can add a new field to this Item object, and call that field “Store”, and as a data type, you will choose a “Store”.

So whereas regular DBs would a string or integer datatype as a reference ID to another table, in Bubble you explicitly specify the type of entity you want to link.

So here’s your basic “table join”.

Furthermore, if you want to link a same item to multiple stores, you can go two ways.

1. Create a relational table like “Inventory” where you associate a Store with an Item.

OR

2. Tick the multiple entries in the Store reference field of your item. This will basically allow you to record multiple stores in that field as an array.
   
   

Personally I would go with option 1, it’s a lot easier down the road.

Display Data
Here you will probably need some repeating group (RG)

If you use the inventory relational table I suggested above, you can search for that as your data element for the RG.

In each cell of your RG, you can decide what to show: store name, item name, quantity etc.

A basic output would be this:

Voila! This should get you started Have fun!

Here’s a demo app, feel free to look at how I setup the data: Bubblinaway | Bubble Editor

[junior.cfaj]

Thank you, bro!

You understood my problem and what you’ve said worked, but I have one more question… How can I show only stores that have this product? For instance, I selected Shoes, I go to this shoes page and see this product in Store A, B, C with their prices.

Shoes

Store A: $10
Store B: $20
Store C: $30

[jaimeisaac]

Maybe on your inventory table add price too. How did you solved?

[grayson]

This was an excellent answer. Tremendously helpful.