Skip to main content
Share / Export

Joins and HQCO Security

JOIN: What It Does (And Why It Matters)​

A JOIN combines rows from two datasets by matching key values. Most of the time, you join because you want more context (more columns) than your base dataset contains.

In Vista reporting, this often looks like:

  • Start with JCCD (job cost detail) for measures like ActualCost / ActualHours
  • JOIN to JCJM (job master) for descriptors like Job Description / PM / status

The mental model​

  • FROM picks the starting dataset (your “left” side)
  • JOIN ... ON ... defines how rows match
  • The JOIN type determines which non-matching rows are kept
SELECT
  -- columns you want
FROM <left_table> AS l
JOIN <right_table> AS r
  ON <match keys>
;

Why JOINs are where reports go wrong​

JOINs don’t only “add columns” — they can also change row count. The most common Vista reporting mistake is accidentally joining a one-row-per-job dataset to a many-rows-per-job dataset and then being surprised when jobs “repeat.”

  • JCJM is usually job-grain (roughly one row per job)
  • JCCD is transaction/detail-grain (many rows per job)

That’s not a bug — it’s the relationship. If you want to come back to job-grain after joining to detail, you usually need GROUP BY.

Vista Join Keys: Start With The “Company Column” (Usually) + The Real Keys​

Most Vista module tables/views include a Company Column. When you join two module datasets, it’s very common that the Company Column is part of the join — but it’s not the only thing you join on.

Think of joins like this:

  • Company Column (often required): prevents cross-company mixing
  • Business key(s): the actual identifier(s) (Job, Phase, CostType, etc.)

Examples in JC:

-- JCCD (detail) → JCJM (job master)
-- Join keys: JCCo + Job
ON d.JCCo = j.JCCo
AND d.Job  = j.Job
-- JCCD (detail) → JCCT (cost type descriptions)
-- Join keys: PhaseGroup + CostType (note: no JCCo in this join)
ON d.PhaseGroup = ct.PhaseGroup
AND d.CostType  = ct.CostType

Important exceptions: many HQ and VA tables are “global” (shared) and may not use a company column the same way module tables do. Always confirm keys in the Schema Visualizer before you join.

Golden rule: never join on descriptions/names unless you’ve proven they’re unique and stable. Prefer real key columns.

JOIN Types (INNER, LEFT, RIGHT, FULL OUTER)​

The four types you’ll see most​

  • INNER JOIN: keep only matches (both sides must match)
  • LEFT JOIN (LEFT OUTER JOIN): keep all left rows, match right when possible
  • RIGHT JOIN (RIGHT OUTER JOIN): keep all right rows, match left when possible
  • FULL OUTER JOIN: keep all rows from both sides, match when possible

Note: in SQL Server, the OUTER keyword is optional. These are equivalent:

LEFT JOIN
LEFT OUTER JOIN

Worked examples (simple, copy/paste)​

Goal: Show active jobs, and show January 2025 cost rows when they exist.

1) INNER JOIN (only jobs that have matching cost rows)​

SELECT *
FROM JCJM AS j
INNER JOIN JCCD AS d
  ON d.JCCo = j.JCCo
 AND d.Job  = j.Job
WHERE j.PRStateCode = 'GA'
  AND d.Mth = '2025-01-01'
;

2) LEFT JOIN (keep all active jobs, even if there are no January cost rows)​

SELECT *
FROM JCJM AS j
LEFT JOIN JCCD AS d
  ON d.JCCo = j.JCCo
 AND d.Job  = j.Job
 AND d.Mth  = '2025-01-01'
WHERE j.PRStateCode = 'GA'
;

Tip: notice the month filter is in the ON clause for LEFT JOIN. If you put d.Mth = '2025-01-01' in the WHERE clause, you would remove the NULL-matching rows and accidentally turn this back into an INNER JOIN.

3) RIGHT JOIN (keep all January cost rows, even if job info is missing due to filtering)​

SELECT *
FROM JCJM AS j
RIGHT JOIN JCCD AS d
  ON d.JCCo = j.JCCo
 AND d.Job  = j.Job
 AND j.PRStateCode = 'GA'
WHERE d.Mth = '2025-01-01'
;

Practical note: RIGHT JOIN is just LEFT JOIN with the tables flipped. Many teams avoid RIGHT JOIN and instead write the equivalent LEFT JOIN starting from the dataset they want to “keep.”

4) FULL OUTER JOIN (keep everything from both sides)​

SELECT *
FROM JCJM AS j
FULL OUTER JOIN JCCD AS d
  ON d.JCCo = j.JCCo
 AND d.Job  = j.Job
 AND d.Mth  = '2025-01-01'
WHERE j.PRStateCode = 'GA'
   OR d.Mth = '2025-01-01'
;

Vista Concept: The “HQ Join” (HQCO) For Company Security​

A common Vista reporting pattern is the HQ Join, which joins your module data to HQCO (HQ Company Setup) using the company column.

When data security is active on the HQCO view, joining to HQCO can restrict results to only the companies a user is allowed to see. Without this, a user who can access module views like JCJM or JCCD may be able to query across companies.

JCJM → HQCO

SELECT
  j.JCCo,
  j.Job,
  j.Description,
  j.PRStateCode,
  j.ProjectMgr
FROM JCJM AS j
INNER JOIN HQCO AS h
  ON j.JCCo = h.HQCo
;

JCCD → HQCO

SELECT
  d.JCCo,
  d.Job,
  d.Mth,
  d.CostTrans,
  d.Phase,
  d.CostType,
  d.Description,
  d.ActualDate,
  d.ActualUnits,
  d.ActualHours,
  d.ActualCost
FROM JCCD AS d
INNER JOIN HQCO AS h
  ON d.JCCo = h.HQCo
;