Skip to content
Table SQL Pack - User Guide
Get starts with the Table SQL Pack for Coda
More
Share
Explore
Get starts with the Table SQL Pack for Coda

icon picker
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
Employees
⁠
and
Customers
⁠
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
1
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
Recent Orders
⁠
table below includes Lookup columns for the Customer and Employee that the order pertains to.
Recent Orders
1
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
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
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
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 (
CtrlP
) instead.