Markdown Tables Reference Guide

Compatibility

Introduced in version 0.16.0. Make sure you are using up to date version. Header-based references and cross-file references introduced in version 0.30.0.

This guide provides detailed documentation on using SQL Seal to query markdown tables within your notes. For a basic introduction, see the Querying Markdown Tables page.

Reference Methods

SQL Seal offers several ways to reference tables in your markdown files:

• By numeric index: table(tableIndex)
• By header name: table(headerName)
• By header name and index: table(headerName, tableIndex)
• From other files: table(file:path/to/file.md, ...)

Referencing Tables by Index

Indexing starts from 0, so the first table in your note is referenced as table(0), the second as table(1), and so on.

TABLE expenses = table(0)

SELECT * FROM expenses

This approach is simple but can be fragile if you add or remove tables from your document, as the indices will shift.

Referencing Tables by Header

Instead of using numeric indices, you can reference tables by the header they appear under, making your queries more resilient to document changes.

Basic Header Reference

If you have a header followed by a table:

# Monthly Expenses
| Date | Category | Amount |
| ---- | -------- | ------ |
| 2025-01-01 | Grocery | 20.40 |

You can reference this table using:

TABLE expenses = table(Monthly Expenses)

This finds the first table that appears after the "Monthly Expenses" header. The reference is case-insensitive, so table(monthly expenses) works too.

Multiple Tables Under the Same Header

If multiple tables exist under the same header, you can specify which one to use with a second parameter:

# Financial Data

## Revenue by Category
| Category | Amount |
| -------- | ------ |
| Product A| 5000   |
| Service B| 7000   |

## Expense Breakdown
| Category | Amount |
| -------- | ------ |
| Salaries | 6000   |
| Rent     | 2000   |

You can reference these tables using either:

-- By subheader
TABLE revenue = table(Revenue by Category)
TABLE expenses = table(Expense Breakdown)

-- Or by parent header and index
TABLE revenue = table(Financial Data, 0)  -- First table under Financial Data
TABLE expenses = table(Financial Data, 1)  -- Second table under Financial Data

Referencing Tables from Other Files

You can reference tables from any markdown file in your vault using the file: prefix.

Basic File Reference

To reference a table in another file by index:

TABLE expenses = table(file:Finance/expenses.md, 0)

This accesses the first table in the Finance/expenses.md file.

File Reference with Header

You can combine file references with header references:

TABLE summary = table(file:Finance/annual-report.md, Revenue Summary)

This finds the table under the "Revenue Summary" header in the Finance/annual-report.md file.

You can also include an index if needed:

TABLE revenue = table(file:Finance/annual-report.md, Financial Data, 0)
TABLE expenses = table(file:Finance/annual-report.md, Financial Data, 1)

File Path Options

When referencing other files, you have these path options:

1. Absolute Paths (from vault root):

   TABLE data = table(file:Finance/reports/q1.md, 0)
   

2. Relative Paths (relative to current file):

   -- Same folder, reports subfolder
   TABLE data = table(file:./reports/q1.md, 0)
   
   -- Parent folder
   TABLE summary = table(file:../summary.md, 0)
   

3. Optional Extension: The .md extension is optional and will be added automatically:

   TABLE data = table(file:Finance/reports/q1, 0)  -- Will look for q1.md
   

Practical Examples

Example 1: Joining Data from Different Files

TABLE sales = table(file:Data/sales.md, Monthly Sales)
TABLE targets = table(file:Plans/targets.md, Monthly Targets)

SELECT 
    s.Month, 
    s.Revenue, 
    t.Target,
    ROUND((s.Revenue / t.Target) * 100, 1) as Achievement
FROM 
    sales s
JOIN 
    targets t ON s.Month = t.Month
ORDER BY 
    s.Month

Example 2: Analyzing Data Under Different Headers

TABLE q1 = table(Q1 Results)
TABLE q2 = table(Q2 Results)
TABLE q3 = table(Q3 Results)
TABLE q4 = table(Q4 Results)

SELECT
    'Q1' as Quarter, SUM(Revenue) as Revenue FROM q1
UNION ALL
SELECT
    'Q2' as Quarter, SUM(Revenue) as Revenue FROM q2
UNION ALL
SELECT
    'Q3' as Quarter, SUM(Revenue) as Revenue FROM q3
UNION ALL
SELECT
    'Q4' as Quarter, SUM(Revenue) as Revenue FROM q4
ORDER BY
    Quarter

Example 3: Expense Summary

TABLE expenses = table(0)

HTML
SELECT 
    strftime('%Y-%m', Date) as Month,
    Category,
    ROUND(SUM(Amount), 2) as Total
FROM 
    expenses
GROUP BY 
    Month, Category
ORDER BY 
    Month, Total DESC

Inline Queries

You can use inline queries to embed values directly in your text:

Total revenue: `S> SELECT SUM(Revenue) FROM sales`.
Average sale: `S> SELECT ROUND(AVG(Amount), 2) FROM transactions`.

Note that inline queries require table definitions elsewhere in the document, as they can't define tables themselves.