Count where previous record exists in either of two tables

Question

I'm trying to COUNT records on one table where both the counted records and their "previous" records exist either in the same table or on another.

The table that I'm sampling has a composite PRIMARY KEY consisting of a bigint and character. The other table has the same structure.

A "previous" record has the same character and a bigint - 1 as the counted record either on the same table or on the other table.

In pseudo:

counting_table
   character_column
   bigint_column

other_table
   character_column
   bigint_column

counting_table will never have a parallel record on other_table because the parallel record on counting_table is deleted when its primary key equivalent is inserted into other_table.

I thought I could do this with subqueries, but I can't seem to figure out how to access the outer query's data from inside the subquery.

How can my intent be implemented optimally?

score 2 · Answer 1 · answered May 08 '14 at 03:50

Inner join on counting_table.bigint-1 = other_table.bigint and counting_table.char = other_table.char

SELECT * 
FROM counting_table t1 
INNER JOIN other_table t2 
    ON t1.bigint_column-1 = t2.bigint_column 
    AND t1.character_column=t2.character_column

Erwin Brandstetter · Accepted Answer · 2019-08-04T22:25:58.847

One way would be to LEFT JOIN both tables (counting_table itself as well as other_table), and count a COALESCE expression, which will be NULL (and therefore not counted) if neither table has a "previous" record:

SELECT count(COALESCE(o.bigint_col, c1.bigint_col)) AS ct
FROM   counting_table c
LEFT   JOIN other_table o ON o.bigint_col = c.bigint_col - 1
                         AND o.char_col   = c.char_col
LEFT   JOIN counting_table c1 ON c1.bigint_col = c.bigint_col - 1
                             AND c1.char_col   = c.char_col;

The other option would be with two OR'ed EXISTS semi-joins:

SELECT count(*) AS ct
FROM   counting_table c
WHERE EXISTS (
   SELECT 1 FROM other_table o
   WHERE  o.bigint_col = c.bigint_col - 1
   AND    o.char_col   = c.char_col
   )
OR    EXISTS (
   SELECT 1 FROM counting_table c1
   WHERE  c1.bigint_col = c.bigint_col - 1
   AND    c1.char_col   = c.char_col
   );

I prefer the second variant as it also works with multiple "previous" rows matching in one of both related tables - which does not seem to apply to your case, but it's still the safer bet. Also typically a bit faster.

Count where previous record exists in either of two tables

2 Answers2

Linked