Which Site Does Each Inspection Belong To?

The inspection log records P1 and the minutes spent on it. The asset register records that P1 belongs to the north site. Botie needs both facts in one report, but copying the site into every old inspection would give us another set of values to maintain.

A join can match the shared identifier and put the relevant fields side by side. We will begin with matching pairs, then ask what changes when one side contains more than one possible match.

Filter the register to active assets, match inspection asset IDs to that register, and project the visit report.

[
  {
    "inspection_id": 101,
    "asset_site": "north",
    "minutes": 30
  },
  {
    "inspection_id": 102,
    "asset_site": "north",
    "minutes": null
  },
  {
    "inspection_id": 103,
    "asset_site": "north",
    "minutes": 0
  },
  {
    "inspection_id": 105,
    "asset_site": "north",
    "minutes": 10
  },
  {
    "inspection_id": 106,
    "asset_site": "north",
    "minutes": 15
  }
]

Inspection row 101 refers to P1. The register row for P1 could appear anywhere in its result. Matching by list position would fail as soon as either list were sorted or filtered.

The join condition names the relationship: the inspection’s asset_id equals the register’s asset_id. That condition should follow a documented key relationship, not whichever columns happen to share some values in the initial fixture.

A join connects identities or stated relationships, not visually aligned rows.

An inner join emits matching pairs. An inspection whose asset matches one register row contributes one output row. If an asset has several inspections, its register fields appear beside each inspection.

Repeating the site in the result is expected because the result grain is still one inspection under this many-to-one match. The database has not created additional stored asset rows.

A join result can repeat fields without indicating duplicate source records.

Aliases such as i for inspections and a for assets shorten qualified names. ON i.asset_id = a.asset_id shows which side provides each key. Qualifying repeated column names also avoids ambiguity.

SELECT chooses which fields of the matched pairs to report. Listing them explicitly helps preserve the intended result interface as either source table evolves.

Read each alias as a table label, not as another stored object.

CREATE TABLE assets (
  asset_id TEXT PRIMARY KEY NOT NULL,
  site TEXT NOT NULL,
  kind TEXT NOT NULL,
  active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE TABLE inspections (
  inspection_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  minutes INTEGER CHECK (minutes >= 0),
  finding TEXT
);
CREATE TABLE readings (
  reading_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  temperature REAL
);
INSERT INTO assets VALUES
 ('P1','north','pump',1), ('P2','north','fan',1),
 ('P3','south','pump',0), ('P4','south','pump',1);
INSERT INTO inspections VALUES
 (101,'P1',30,'loose'), (102,'P1',NULL,NULL),
 (103,'P2',0,'clear'), (104,'P3',20,'clear'),
 (105,'P2',10,'loose'), (106,'P1',15,'loose');
INSERT INTO readings VALUES
 (1,'P1',70.0), (2,'P1',80.0), (3,'P2',NULL), (4,'P3',65.0);

SELECT i.inspection_id, a.site, i.minutes
FROM inspections AS i
JOIN assets AS a ON a.asset_id = i.asset_id
ORDER BY i.inspection_id;

The challenge requests visits for active equipment. Filter on the register’s active field while retaining visits from its matching assets. The inactive P3 record exists, but it does not belong to this requested population.

For an inner join, moving an ordinary row condition between ON and WHERE can be equivalent. That reasoning does not transfer unchanged to outer joins, where unmatched rows are retained. We will make that difference visible next.

A missing row in the final report may have failed a filter rather than failed the key match.

The asset table’s unique key establishes at most one register match per inspection. Joining on kind would not: several pumps share that description. A visit could then pair with multiple unrelated pump rows.

Likewise, a join on only one part of a compound key can mix different sites or tenants. Include all columns needed to establish the intended identity.

A join condition is a claim about relationships. Constraints provide evidence for that claim.

A row without a matching partner is absent from an inner result. Our enforced foreign key prevents an orphan inspection in the ordinary fixture, but a filtered register can still remove its potential partner. Other data sources may have incomplete mappings.

If the report needs to show unmatched records, an inner join is the wrong operation for that requirement. Use an explicit unmatched-preserving construction and inspect its missing fields.

A complete-looking matched report can hide records that never found a partner.

Attaching site metadata to sensor or inspection rows is a common feature-building step. Verify that the match preserves the intended observation count and that the metadata reflects the time the prediction would be made.

A current site assignment can be inappropriate for an old observation if the asset moved. Historical joins and availability boundaries require additional records and conditions, which later lessons will address.

Getting a key match is necessary, but it does not establish that the attached value was available at prediction time.