Join two tables on consecutive segments

Question

I want to join two tables, where the first table has more entries than the second, such that rows from each are joined in order. Maybe a little example would be helpful:

Table T:

| tid | sid | ron | val | seqno |
| --- | --- | --- | --- | ---   |
| 1   | a   | x1  | 15  | 1     |
| 2   | b   | x2  | 10  | 3     |
| 2   | b   | x3  | 20  | 4     |
| 3   | a   | x5  | 10  | 5     |
| 4   | c   | x9  | 15  | 7     |
| 4   | c   | x9  | 15  | 8     |
| 4   | c   | x9  | 20  | 10    |
| 4   | c   | x9  | 15  | 11    |
| 6   | b   | x11 | 22  | 12    |
| 7   | b   | x12 | 10  | 14    |
| 7   | b   | x13 | 10  | 16    |
| 7   | b   | x13 | 10  | 17    |
| 7   | b   | x14 | 10  | 19    |

The second table (Table C) is as follows (in reality, more columns):

| tid | sid | ron | val | fid |
| --- | --- | --- | --- | --- |
| 2   | b   | x3  | 20  | 54  |
| 4   | c   | x9  | 15  | 12  |
| 4   | c   | x9  | 15  | 14  |
| 4   | c   | x9  | 20  | 15  |
| 4   | c   | x9  | 15  | 20  |
| 7   | b   | x13 | 10  | 112 |
| 7   | b   | x13 | 10  | 113 |

seqNo and fid are there in each table to provide ordering within the groups formed by (tid, sid, ron), and that is the ordering I'd like to maintain.

How can I get from these two tables to something like the following table?

| tid | sid | ron | val | fid | seqno |
| --- | --- | --- | --- | --- | ---   | 
| 2   | b   | x3  | 20  | 54  | 4     |
| 4   | c   | x9  | 15  | 12  | 7     |
| 4   | c   | x9  | 15  | 14  | 8     |
| 4   | c   | x9  | 20  | 15  | 10    |
| 4   | c   | x9  | 15  | 20  | 11    |
| 7   | b   | x13 | 10  | 112 | 16    |
| 7   | b   | x13 | 10  | 113 | 17    |

I can't assign a rank to each element in the group and use that for matching inside of a LEFT JOIN, since there are cases where matching doesn't begin at the end of the group (for example tid=7). Also, because val in the same group may have repeated values, I can't blindly match on it either, as that may blow up the number of rows.

Yes, `seqno` is only in the first table to provide ordering, and similarly there is `fid` in the second table to provide ordering. These two can't be joined on, as they contain different values. — Nikola Knezevic, Jul 21 '21 at 22:03

score 0 · Answer 1 · answered Jul 22 '21 at 06:47

This is what I managed to get late last night, seems to be working correctly:

WITH
  table_t AS (
          SELECT *
          FROM (VALUES
            (1,'a','x1',15,1),
            (2,'b','x2',10,3),
            (2,'b','x3',20,4),
            (3,'a','x5',10,5),
            (4,'c','x9',15,7),
            (4,'c','x9',15,8),
            (4,'c','x9',20,10),
            (4,'c','x9',15,11),
            (6,'b','x11',22,12),
            (7,'b','x12',10,14),
            (7,'b','x13',10,16),
            (7,'b','x13',10,17),
            (7,'b','x14',10,19)
          ) AS c(tid, sid, ron, val, seq)
   ),
   table_t_ranked AS (
       SELECT *
       , DENSE_RANK() OVER (PARTITION BY tid, sid, ron ORDER BY seq ASC) AS ranking
       FROM table_t
   ),
   table_c AS (
           SELECT *
           FROM (VALUES
            (2,'b','x3',20,54),
            (4,'c','x9',15,12),
            (4,'c','x9',15,14),
            (4,'c','x9',20,15),
            (4,'c','x9',15,20),
            (7,'b','x13',10,112),
            (7,'b','x13',10,113)
           ) AS c(tid, sid, ron, val, fid)
   ),
    table_c_ranked AS (
       SELECT *
       , DENSE_RANK() OVER (PARTITION BY tid, sid, ron ORDER BY fid ASC) AS ranking
       FROM table_c
   ),
   foo AS (
       SELECT c.*
        , t.seq
        , t.ranking as ranking_t
        FROM table_c_ranked c
        LEFT JOIN table_t_ranked t
        ON c.tid = t.tid
        AND c.sid = t.sid
        AND c.ron = t.ron
        AND c.val = t.val
   )

SELECT tid, sid, ron, val, fid, seq
FROM foo       
WHERE ranking = ranking_t
ORDER BY tid, seq

is it just me or indeed SO is now a one way goes only – NKAT Apr 15 '22 at 19:11 — NKAT, Apr 15 '22 at 19:11

Join two tables on consecutive segments

1 Answers1