Which Assets Have No Matching Inspection?

Botie joins the asset register to the inspection log, and P4 disappears. That is awkward: the supervisor specifically needs to notice equipment with no recorded visits. A report containing only matches hides the absence they were looking for.

A left join begins from the rows we must retain. When a register entry finds no qualifying visit, SQL keeps the asset and supplies missing values for the visit fields. We will make that placeholder visible before counting anything.

Filter the visits to loose findings, left-join them onto assets, then count real visit identifiers per asset.

[
  {
    "asset_id": "P1",
    "loose_visits": 2
  },
  {
    "asset_id": "P2",
    "loose_visits": 1
  },
  {
    "asset_id": "P3",
    "loose_visits": 0
  },
  {
    "asset_id": "P4",
    "loose_visits": 0
  }
]

The report must contain every asset, so assets belongs on the left of LEFT JOIN. Reversing the tables preserves visits instead and answers a different question.

A matched asset can still produce several rows when it has several visits. The unmatched asset produces one row with missing right-side fields. Preserving all left records does not mean producing exactly one result row per left record.

Retention and multiplicity are separate properties of a join.

Look at the raw left-join output. P4 is present, but its inspection_id is NULL because there was no matched visit. That NULL came from the join operation, not from a stored inspection with a missing primary key.

A non-null key on the right table is useful evidence: if that key is NULL in the outer result, the row has no right partner. Testing a nullable measurement instead would confuse an actual visit with an unknown duration and an absent visit.

Use a right-side identity field to distinguish absent records from missing measurements.

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 a.asset_id, i.inspection_id
FROM assets AS a LEFT JOIN inspections AS i ON i.asset_id = a.asset_id
ORDER BY a.asset_id, i.inspection_id;

The new report counts loose-component visits while retaining all assets. Add finding = loose to the join condition, or filter the visit relation before joining. Then an asset with only clear visits has no qualifying partner but remains on the left.

Putting i.finding = loose in WHERE after the left join removes unmatched rows, because their right-side finding is NULL and the comparison is unknown. That would hide precisely the zero-match assets the report needs.

ON defines what counts as a match; WHERE filters the result rows after unmatched placeholders have been formed.

After grouping by the asset identity, COUNT(i.inspection_id) counts actual matched visits. COUNT(*) counts every joined result row, including the one retained for an unmatched asset.

COUNT(i.minutes) asks yet another question: how many matched visits have a known duration? An existing visit with NULL minutes should still count as a visit. Choose the expression according to what the report is counting.

A non-null record identity is a better count target than a measurement that may be absent.

A zero count of matched visits is meaningful evidence about this query’s matching condition. It can mean no visits at all, or visits exist but none have the requested finding. It does not establish that the asset is healthy or that nobody performed unrecorded work.

If we also sum minutes, a NULL sum can arise from no matches or all matched durations being unknown. Keep an actual-visit count and a known-duration count when that distinction matters.

The data can establish an absence of recorded evidence under a stated query, not every real-world absence.

Group and display the asset_id from the left register. Grouping by the right inspection asset_id would collect all unmatched assets under a single NULL key. Their identities would disappear even though the join retained them.

This is another reason to qualify column names with aliases: the same spelling can carry different nullability and meaning after an outer join.

Keep the identity of the population you promised to preserve.

A good changed case gives one asset several clear visits and another no visits. Both should receive zero loose findings, while remaining separate asset rows.

That case detects a right-side condition placed after the join and a COUNT(*) shortcut. Retest an asset with actual loose visits to ensure the repair has not reduced every count to zero.

The output must retain the whole register and count only real qualifying partners.