How to create a view from inner join

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! :handshake:

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