Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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
   );
share|improve this answer
    
@Gracchus: It depends on your environment. Test both with EXPLAIN ANALYZE. Run a couple of times and take the best time to exclude caching artifacts. My money is on EXISTS ... Drop a line here as to the outcome if you don't mind. –  Erwin Brandstetter May 8 at 13:46
1  
@Gracchus: Nitpicking: EXISTS semi-joins are not exactly subqueries (that shows in the query plan). Results would be welcome! –  Erwin Brandstetter May 8 at 15:06

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.