Joining RRC datasets — the practical recipes

Everything in the previous articles was preparation for this one. The RRC's value is not in any single file; it is in the fact that the files describe the same wells, operators and leases, and can be put back together.

Below are the joins that matter, each with its cardinality and its traps. The SQL targets the warehouse table names EZRRC serves; the same logic applies whatever you load the files into.

Before anything: three universal traps

Type mismatches. The same identifier is stored as text in one file and as a number in another. The P-5 operator number is the worst offender: text with leading zeros in the production tables, an integer in the operator dimension. A join between them without a cast returns zero rows, and it returns them without complaint.

Lost leading zeros. Any identifier read as an integer and written back as text has lost its padding. County 001 becomes 1; district 08 becomes 8; lease 00123 becomes 123. Normalise once, at load, and pad to the documented width.

Fan-out. Most of these joins are one-to-many. Join a wellbore to its perforations and you get one row per perforation; aggregate a depth column across that and every well with ten perforations counts ten times. Decide whether you want the parent grain or the child grain before you write the join, and aggregate the child side in a subquery when you want the parent.

1. Permits to wellbores — did it get drilled?

SELECT p.permit_no,
       p.issue_date,
       p.spud_date,
       w.api_number IS NOT NULL AS in_wellbore_db,
       w.total_depth
  FROM texas.permits  p
  LEFT JOIN texas.wellbore w ON w.api_number = p.api_number
 WHERE p.issue_date >= DATE '2025-01-01'
   AND p.application_type IN ('DRILL', 'DRILL-HORIZONTAL');

Cardinality: many permits to one wellbore. Amendments, re-entries and recompletions all target an existing hole.

Traps: p.api_number is null on permits issued before an API number was assigned — use a left join or you will silently drop them. Both sides must use the same API rendering; EZRRC normalises to 42-XXX-YYYYY everywhere, but if you are working from raw files, normalise to the eight-digit core first.

2. Wellbore to its child records

SELECT w.api_number,
       w.total_depth,
       perf.shallowest,
       perf.deepest,
       perf.intervals
  FROM texas.wellbore w
  LEFT JOIN (
        SELECT api_county, api_unique,
               min(from_perf) AS shallowest,
               max(to_perf)   AS deepest,
               count(*)       AS intervals
          FROM texas.wb_perforations
         GROUP BY api_county, api_unique
  ) perf ON (perf.api_county, perf.api_unique) = (w.api_county, w.api_unique)
 WHERE w.field_district = '08';

Cardinality: one wellbore to many casings, perforations, formations, plugging records, permits and H-15 reports.

Traps: the composite key is (api_county, api_unique), not a formatted API string — that is how the file stores it and it is what the child tables carry. Aggregate the child side in a subquery, as above, to keep the result at wellbore grain.

3. Anything to operators

SELECT o.name, o.p5_status, count(*) AS permits
  FROM texas.permits p
  JOIN texas.operators o ON o.id = p.operator_no::int
 WHERE p.issue_date >= DATE '2026-01-01'
 GROUP BY o.name, o.p5_status
 ORDER BY permits DESC
 LIMIT 25;

Cardinality: many rows to one operator.

Traps: the cast. texas.operators.id is an integer P-5 number; permit and production files carry it as zero-padded text. Cast one side, consistently, and prefer casting the text to integer over padding the integer to text — it is index-friendlier and it will not silently mismatch on width.

Never join on operator name. See the operators article for the six reasons.

4. Production to wells — the bridge join

This is the one people ask for and assume does not exist. It does. The PDQ well completion table carries production keys and API keys on the same row.

SELECT wc.api_county_code || wc.api_unique_no AS api8,
       lc.cycle_year_month,
       lc.lease_oil_prod_vol,
       lc.lease_gas_prod_vol
  FROM texas.pdq_og_well_completion wc
  JOIN texas.pdq_og_lease_cycle     lc
    ON  lc.oil_gas_code = wc.oil_gas_code
    AND lc.district_no  = wc.district_no
    AND lc.lease_no     = wc.lease_no
 WHERE wc.api_county_code = '329'
   AND lc.cycle_year_month BETWEEN '202501' AND '202612';

Cardinality: many wells to one lease-cycle row. This is the crucial point.

Trap — and it is the big one: this join does not give you per-well production. It gives you, for each well, the production of the lease that well belongs to. A ten-well lease produces ten identical volume rows. Summing them multiplies the lease's output by ten.

Use this join to answer "which leases does this API number belong to" and "which API numbers roll up into this lease's volumes". If you need per-well oil volumes, you are into allocation, and allocation is a model, not data. Say so out loud whenever you publish a number that came from one.

Gas is the exception that proves the rule: gas production is already per gas well, so for oil_gas_code = 'G' the lease row is a well row. Handle the two codes separately.

5. Leases to their metadata

SELECT rl.lease_no, rl.lease_name, rl.operator_name,
       rl.field_no, rl.field_name, rl.well_no
  FROM texas.pdq_og_regulatory_lease_dw rl
 WHERE rl.district_no = '08'
   AND rl.oil_gas_code = 'O';

Cardinality: one row per lease per schedule.

Trap: the lease key is the triple (oil_gas_code, district_no, lease_no). Lease numbers repeat across districts and across the oil and gas schedules. Every lease join needs all three columns.

6. Fields

SELECT fd.field_no, fd.field_name, fd.district_no,
       fd.wildcat_flag, fd.o_discovery_dt, fd.g_discovery_dt
  FROM texas.pdq_og_field_dw fd
 WHERE fd.field_no = '16481001';

Cardinality: many leases and wells to one field.

Traps: field numbers are eight digits and zero-padded. The field dimension carries separate oil and gas attributes for the same field number — discovery dates, rule types, offshore and salt dome flags — because a field can be scheduled differently for each product. Pick the side that matches your oil_gas_code. Exclude or bucket WILDCAT before ranking fields by anything.

7. Counties and districts

SELECT c.county_no, c.county_name, c.county_fips_code,
       c.district_no, c.district_name, c.on_shore_flag
  FROM texas.pdq_gp_county c
 ORDER BY c.county_name;

This is the crosswalk between RRC county numbers, FIPS codes, county names and districts. Use it instead of writing your own mapping. Join on the number; never on the name.

8. Well status, the quick way

When you want current status per well and do not need downhole structure, the Wellbore Query extract is one flat row per well:

SELECT wq.api_no, wq.operator_name, wq.well_type_name,
       wq.plug_date, wq.curr_inact_yrs, wq.h15_status_code
  FROM texas.wellbore_query wq
 WHERE wq.district = '08'
   AND wq.plug_date IS NULL;

Trap: api_no here is the eight-digit core, not a formatted API. Normalise before joining it to anything else.

9. Completions to their children

SELECT wc.api_number,
       count(DISTINCT cf.id) AS formations,
       count(DISTINCT cc.id) AS casing_strings
  FROM texas.well_completions       wc
  LEFT JOIN texas.cmpl_formation_data cf ON cf.completion_id = wc.id
  LEFT JOIN texas.cmpl_casing_data    cc ON cc.completion_id = wc.id
 GROUP BY wc.api_number;

Cardinality: one completion filing to many formations, casing strings, production intervals and stimulation records.

Trap: counting two child tables in one query without DISTINCT produces a cross product. Either use count(DISTINCT ...) as above or aggregate each child in its own subquery.

A cardinality cheat sheet

Validating a join

Before you trust a result, run these four checks:

1. Row count before and after. A join that multiplies your row count has fanned out; a join that shrinks it has dropped non-matches. Both may be correct — but you should know which happened.
2. Match rate. count(*) FILTER (WHERE right_side.key IS NULL) on a left join. A 3% miss might be genuine history. A 97% miss is a padding or type bug.
3. Spot check one well. Pick an API number, pull every row it produces across every joined table, and read it. Compare against the RRC's own public search.
4. Check the totals against a pre-aggregated table. If you aggregated lease-cycle rows to a district, compare with the district-cycle table. They should agree. If they do not, your grain is wrong.

EZRRC records every one of these relationships as data — source dataset, target dataset, join columns, cardinality and a plain-English description — so each dataset page can tell you what it connects to, and so the cart can point out that the file you just added needs a second file to be useful.

Next: doing all of this over HTTP, without downloading anything.