Joining Your Data with WITH

The concept of associations

In HubSpot, objects are linked to each other: a contact belongs to a company, a company has deals, a deal can be associated with tickets. These links are called associations.

WITH ASSOCIATED lets you include these linked objects directly in your query, without having to perform multiple separate extractions.

LEFT OUTER: all companies, even without contacts

By default, WITH ASSOCIATED is a LEFT OUTER JOIN: you get all primary objects, even those with no associated objects (their list will simply be empty).

SELECT name, domain
FROM company
WITH ASSOCIATED contact AS contacts
  SELECT email, firstname
  WHERE email HAS_PROPERTY

Reading: "All companies, with their contacts who have an email filled in."

The result contains:

• name and domain of the company
• The contacts list with email and firstname for each eligible contact
• For companies without contacts: the contacts list is empty

INNER: only companies with associated objects

WITH INNER ASSOCIATED is an INNER JOIN: only companies that have at least one matching associated object are returned.

SELECT name, domain
FROM company
WITH INNER ASSOCIATED deal AS won_deals
  SELECT amount, dealstage
  WHERE dealstage = 'closedwon'

Reading: "Only companies that have at least one closed-won deal."

Note: WITH INNER ASSOCIATED is a shorthand for WITH ASSOCIATED ... REQUIRE COUNT > 0. Both forms are equivalent.

Multiple WITH on the same query

You can chain multiple WITH blocks on the same primary object:

SELECT name, domain
FROM company
WHERE annualrevenue > 1000000
WITH ASSOCIATED contact AS contacts
  SELECT email, firstname
  WHERE email HAS_PROPERTY
WITH INNER ASSOCIATED deal AS closed_deals
  SELECT dealstage, amount
  WHERE dealstage = 'closedwon'
    AND amount > 5000

Reading: "Companies with more than €1M in revenue, with their contacts (having an email) AND their closed-won deals above €5,000. Only companies with at least one matching deal are returned."

Performance tip: When you have multiple WITH blocks, RevOpsQL performs only one association query per object type. If two WITH blocks target contacts, the data is fetched in a single call.

WHERE inside a WITH block

The WHERE inside a WITH block filters the returned associated objects, but does not filter the primary object.

WITH ASSOCIATED deal AS active_deals
  SELECT dealname, amount
  WHERE dealstage != 'closedwon'

Result: the company is still included, but its active_deals list only contains its non-won deals.