Hi, I have two tables in my database:
- Food Items (contains a number of food items with various attributes such as price, food type, etc.)
- Shops (contains a list of shops with attributes such as address, post code)
These would have a many to many relationship, in that each food item could be in many shops and each shops could contain many food items.
I would like to make a third table which automatically contains all possible combinations of the two tables, and which automatically updates whenever a new record is added to either of the first two tables. Then I intend to add some other attributes and set up a filter so that I could query it.
I can not work out how to do this. I have found a way to create a junction table by following this tutorial ( https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relationships#ex3 ) but it seems to require me selecting each line manually. I must be doing something wrong but can not work out what it is.
Edit to further clarify: I think in SQL it would be a query along the lines of SELECT * FROM food_items CROSS JOIN shops.