Being able to join data across multiple tables is a powerful feature of SQL, and you can use this Pack to join data across Coda tables.
Coda has built-in formulas and column types that allow you to combine data across tables, and whenever possible you are better off using them. Joining tables with this Pack is great when you want to use a type of join logic not supported by Coda, or want to easily join data across docs.
tables contain a Country column with matching values. If you wanted to get a list of all of the countries where there is at least one employee and customer, you could join the tables as shown below.
table below includes Lookup columns for the Customer and Employee that the order pertains to.
Recent Orders
1
Order ID
Customer
Employee
Order ID
Customer
Employee
1
11070
LEHMS
Fuller
2
11074
SIMOB
King
3
11075
RICSU
Callahan
4
11076
BONAP
Peacock
5
11077
RATTC
Davolio
No results from filter
On matching column
By default, Lookup columns will be loaded into SQL as text, using the display value of the row. For example, “LEHMS” for the customer
@LEHMS
. You can join that to the “Customer ID” column of the customers table.
Query
Result
Query
Result
1
SELECT
`Order ID`,
Contact
FROM `Recent Orders` RO
JOIN Customers C
ON C.`Customer ID` = RO.Customer
11070Renate Messner
11074Jytte Petersen
11075Michael Holz
11076Laurence Lebihan
11077Paula Wilson
There are no rows in this table
However it’s easy for the user to change the display value of a row, which will break your query.
On display value
Each loaded SQL table includes the special column _display, containing the display value for that row. Joining on that column will ensure a match even if the display column is later changed.
Query
Result
Query
Result
1
SELECT
`Order ID`,
Contact
FROM `Recent Orders` RO
JOIN Customers C
ON C._display = RO.Customer
11070Renate Messner
11074Jytte Petersen
11075Michael Holz
11076Laurence Lebihan
11077Paula Wilson
There are no rows in this table
This option is not quite foolproof however, as there is nothing preventing multiple rows in a Coda table from having the same display value.
On row ID
To prevent collisions between rows with the same display value, a safer approach is to enable the parameter useRowIds. This changes how Lookup column values are loaded, storing the ID of the referenced row instead of it’s display value. For example, “i-OY7278FNnT” for the customer
@LEHMS
.
Additionally, each loaded SQL table includes the special column _id, containing the unique ID for that row. Joining a these IDs is the safest option when dealing with lookups.
Query
Use Row IDs
Result
Query
Use Row IDs
Result
1
SELECT
`Order ID`,
Contact
FROM `Recent Orders` RO
JOIN Customers C
ON C._id = RO.Customer
11070Renate Messner
11074Jytte Petersen
11075Michael Holz
11076Laurence Lebihan
11077Paula Wilson
There are no rows in this table
Want to print your doc? This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (