Skip to main content
Share / Export

Welcome to SQL Foundations

This article adapts the vetted SQL Foundations beginner course into a docs starting point for querying Vista.

Why These Foundations Exist​

The Vista database is powerful — and intimidating. It’s common to have 1,000+ tables and 25,000+ columns available, with naming patterns and security rules that aren’t obvious to new users.

That complexity creates a few everyday problems:

  • People spend time exporting grids, copying/pasting, and reconciling spreadsheets.
  • “Simple” questions (like "give me my job list") turn into custom report requests.
  • SQL courses exist everywhere, but they don’t explain how to apply SQL thinking to Vista concepts like Views vs Tables, the Company/Group Columns, HQCO security joins, and posting month.

These foundations fix that: you’ll learn the basic SQL building blocks and immediately apply them to Vista’s Job Cost (JC) module.

How To Follow Along (Tools + Access)​

No prior SQL required. This track is designed for all experience levels, and teaches SQL the same way it’s commonly used against the Vista database.

Preferred workflow:

  • Use the internal Schema Visualizer to browse the View list and inspect columns + key metadata.
  • Use the internal SQL Editor to write and run queries.
  • Use SSMS (SQL Server Management Studio) as a fallback if you don’t have access to the in-app tools.

SQL Editor note: the SQL Editor requires the Sandbox Bridge or SQL Bridge to be active to run queries properly. You can confirm you’re connected by checking the WiFi icon in the upper-right of the app.

Safety: these articles focus on read-only SELECT queries.

Use The Schema Visualizer To Explore Vista Views​

tip

The internal Schema Visualizer is one of the fastest ways to learn the Vista database. It gives you access to 1000+ Vista Views, and when you select a View it shows helpful properties (columns and key metadata) you can use to write correct SQL.

Use it when you’re thinking:

  • “What does this View contain?”
  • “What’s the company column / key fields?”
  • “How does this View join to other tables?”

What You’ll Be Able To Do​

By the end of these foundations, you’ll be able to pull answers directly from Vista using safe, readable SQL patterns. You’ll know how to choose the right base dataset and avoid common reporting mistakes in Job Cost.

  • Understand View → Table and why using Views (JCJM, JCCD) is common for reporting.
  • Write readable SELECT queries to pull exactly the rows/columns you need.
  • Identify the Company Column (JC uses JCCo) and join correctly between multiple tables.
  • Filter to the correct posting period using JCCD.Mth (the GL/Accounting month).
  • Apply the HQCO join to ensure results are restricted to the companies a user is allowed to see when company data security is active.

A Quick Win (Why SQL Is Worth It)​

A simple query can replace a lot of manual work. For example, the ability to run something like this can produce a full job list without exporting a grid or waiting on a custom report:

-- Example (read-only): pull a job list
SELECT
  j.JCCo,
  j.Job,
  j.Description,
  j.PRStateCode,
  j.ProjectMgr
FROM JCJM AS j
;

In the following articles, you’ll learn how to add:

  • WHERE filters (e.g., status, specific jobs, posting months)
  • The HQCO join for company security
  • JOIN + GROUP BY to summarize JCCD costs by job

Here’s a more complex example that summarizes Job Cost detail by job, phase, and cost type for Company 1 across all months in 2025:

SELECT JCCostDetail.JCCo,
  JCCostDetail.Job,
  Jobs.Description [Job Desc],
  JCCostDetail.Phase,
  JobPhases.Description [Phase Desc],
  JCCostDetail.CostType,
  JCCostTypes.Description [CostType Desc],
  SUM(JCCostDetail.ActualCost) [Actual Cost]
FROM JCCD JCCostDetail
JOIN HQCO HQCompany ON JCCostDetail.JCCo=HQCompany.HQCo
JOIN JCJM Jobs ON JCCostDetail.JCCo=Jobs.JCCo AND JCCostDetail.Job=Jobs.Job
JOIN JCJP JobPhases ON JCCostDetail.JCCo=JobPhases.JCCo AND JCCostDetail.Job=JobPhases.Job AND JCCostDetail.Phase=JobPhases.Phase
JOIN JCCT JCCostTypes ON JCCostDetail.PhaseGroup=JCCostTypes.PhaseGroup AND JCCostDetail.CostType=JCCostTypes.CostType
WHERE JCCostDetail.JCCo = 1
  AND JCCostDetail.Mth BETWEEN '2025-01-01' AND '2025-12-01'
GROUP BY JCCostDetail.JCCo
  , JCCostDetail.Job
  , Jobs.Description
  , JCCostDetail.Phase
  , JobPhases.Description
  , JCCostDetail.CostType
  , JCCostTypes.Description