Skip to content
Get starts with the Table SQL Pack for Coda
Joining tables
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.
Join with matching values
Both the and 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.
Query
Result
SELECT C.Country
FROM Customers C
JOIN Employees E
ON C.Country = E.Country
GROUP BY C.Country
UK
USA
There are no rows in this table
Join with lookups
The table below includes Lookup columns for the Customer and Employee that the order pertains to.
Recent Orders
Order ID
Customer
Employee
11070
LEHMS
Fuller
11074
SIMOB
King
11075
RICSU
Callahan
11076
BONAP
Peacock
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 . You can join that to the “Customer ID” column of the customers table.
@LEHMS
Query
Result
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
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
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.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.