Joining tables

This page explains how RevenueBase data feed tables relate to each other, which keys to use for joins, and common pitfalls.

Companies is the primary entity table for organizations.
Contacts (people) reference Companies via a foreign key (e.g., company_id).
Insights and Historical Experience can reference Contacts and/or Companies.

Document each relationship on the relevant table pages and summarize here.

From table	To table	Join key	Relationship
Contacts	Companies	`contacts.company_id = companies.id`	Many-to-one
Insights	Contacts	`insights.contact_id = contacts.id`	Many-to-one
Insights	Companies	`insights.company_id = companies.id`	Many-to-one
Historical Experience	Contacts	`historical_experience.contact_id = contacts.id`	Many-to-one

Use these keys in your JOIN conditions. Column names may differ in your real schema; adjust accordingly.

Contacts to Companies (many-to-one):

SELECT c.id, c.email, co.name AS company_name
FROM contacts c
JOIN companies co ON co.id = c.company_id;

Include company rows with no contacts (LEFT JOIN):

SELECT co.name, c.email
FROM companies co
LEFT JOIN contacts c ON c.company_id = co.id;

Contacts with Insights:

SELECT c.email, i.insight_type, i.value
FROM contacts c
JOIN insights i ON i.contact_id = c.id;

Cardinality — Contacts may have many rows per Company. Aggregating or limiting per entity can avoid duplicates in reporting.
Nulls — Foreign key columns are usually non-null when the relationship is required; optional relationships may allow nulls. Check the Data dictionary and table pages.
Performance — Join on indexed columns (typically primary and foreign keys). Large tables may require filters or limits during development.
Pre-joined views — For common combinations (people + companies, or + insights), use Pre-Joined Tables instead of writing joins yourself.

​Joining tables