How can I create a view from an inner join, i.e. combine rows from two tables with one column of values in common?
Customers table: CustomerID
Orders table: OrderID, CustomerID
Show all orders by joining on CustomerID:
Order1, Customer1
Order2, Customer1
Order3, Customer2
Order4, Customer2
Hi @Andrew_Malcolmson and welcome to this community!
I’d suggest you to consider Coda more on a object-oriented philosophy rather than a key-based relationships.
Please, have a look at this simple example based on your use-case.
As you can see, you query your data by providing the actual “object” (i.e. Row
), rather than a key.
this also simplifies dynamic filtering when building up views.
Note that I just added ID
columns for the sake of the relational heritage, but they are not really needed.
Let me know if this helps.
Cheers!
Edit:
If you really need and want ID references, you can still build up your filter like this:
e.g. “retrieving the Customer of the first Order”:
Customers.Filter(ID=Orders.First().[Customer ID])
[or the equivalent Customers.Lookup(ID,Orders.First().[Customer ID])
]
or “retrieving all Orders of the first Customer”
Orders.Filter([Customer ID]=Customers.First().ID)
3 Likes
That’s great Fredrico. Your example showed me I needed to reformat the Customers column in the Orders table as a lookup to the Customers table.
I’ll study the rest of your answer soon.
1 Like