0

I'm trying to join two tables and filter join results but I couldn't manage it and I'd appreciate any help.

I have these row sets:

Table A
id | userid | targetid | start_date
-----------------------------------
11 | user1  | 123      | 22/10/2019
22 | user1  | 123      | 02/10/2019
33 | user1  | 123      | 04/10/2019
44 | user1  | 456      | 02/10/2019
55 | user1  | 123      | 13/11/2020

Table B
id | targetid | start_date
---------------------------
66 | 123      | 21/10/2019
77 | 456      | 11/11/2020
88 | 123      | 11/11/2020
99 | 123      | 12/11/2020

What I'm trying to do is to find most recent Table B row for each Table A row using targetid as FK and using start_date as filter/order values. Here's the result I'm looking for:

11 | user1  | 123      | 66
22 | user1  | 123      | 66
33 | user1  | 123      | 66
44 | user1  | 456      | 77
55 | user1  | 123      | 99

I tried to inner join and limit join condition using on ... and a.start_date <= b.start_date but that made Table A | 55 join with each row in Table B.

This could be any type of SQL, join/cursor/loop the method and performance doesn't matter.

SQL Fiddle

5
  • 1
    Please post the full SELECT that you tried. A table of expected results would also be nice. Nov 12, 2020 at 12:31
  • using targetid as FK targetid is not unique in table b - what date must be taken for a.start_date <= b.start_date condition?
    – Akina
    Nov 12, 2020 at 12:56
  • i thought about joining a and bby month and year, but that is not possible because targetid 456 has different month.and the target id allne i can't take, that is not unique sqlfiddle.com/#!4/543f0/30
    – nbk
    Nov 12, 2020 at 13:09
  • There's no answer to this because your choice of "most recent" isn't consistent across rows. If we assume we're taking the nearest row active on or after, then id = 11 from Table A should map to id = 99 from Table B, not 66. So by "most recent do you mean: 1. minimum date greater than or equal to or 2. Date separated by the fewest days?
    – bbaird
    Nov 12, 2020 at 22:32
  • @bbaird: with most recent I mean tableb.row with greatest date which is equal or smaller than current tablea.row's date.
    – dstr
    Nov 13, 2020 at 6:48

2 Answers 2

1

So there are two ways to do this:

  1. If you have keys that guarantee that combinations of (targetId,start_date) are unique, a correlated subquery will return consistent results
  2. If you cannot guarantee uniqueness, you will have to use a partitioning function in order to guarantee the same result is returned each time the query is executed

If a unique index exists:

SELECT
  a.id
 ,a.userid
 ,a.targetid
 ,b.id AS b_id
 /* And other columns from Table B */
FROM
  table_a a
LEFT JOIN
  table_b b
    ON b.targetid = a.targetid
        AND b.start_date =
          (
            SELECT
              MAX(start_date)
            FROM
              table_b
            WHERE
              targetid = a.targetid
                AND start_date <= a.start_date
          )

No unique index exists:

For this I'm assuming Id on Table B is a row identifier and unique. If it isn't, you will need to add additional columns to the SORT portion of the PARTITION statement to guarantee the query is deterministic.

SELECT
  id
 ,userid
 ,targetid
 ,b_id
 /* Other columns from table b */
FROM
  (
    SELECT
      a.id
     ,a.userid
     ,a.targetid
     ,b.id AS b_id
     /* Other columns from Table B */
     ,ROW_NUMBER()
        OVER
          (
            PARTITION BY
              a.id
            ORDER BY
              b.start_date DESC
             ,b.id DESC
          ) AS rownum
    FROM
      table_a a
    LEFT JOIN
      table_b b
        ON b.targetid = a.targetid
            AND b.start_date <= a.start_date
  ) x
WHERE
  rownum = 1

Notes:

As I mentioned in my earlier comment, the desired results you provided are inconsistent with the requirement tableb.start_date <= tablea.start_date, specifically for Ids 22, 33, 44.

0
WITH CTE AS (
SELECT a.id, a.userid, a.targetid, b.id AS b_id,
    ROW_NUMBER() OVER (PARTITION BY  a.id, a.userid, a.targetid ORDER BY b.start_date) AS rn      
FROM a
JOIN b ON a.targetid = b.targetid AND b.start_date >= a.start_date
)
SELECT  id, userid, targetid, b_id
FROM CTE WHERE rn = 1
2
  • Would this work on 11g? SQL Fiddle gives ORA-00907: missing right parenthesis error.
    – dstr
    Nov 13, 2020 at 6:40
  • @dstr I modified the query (it seems Oracle 11g doesn't allow to use order by in this type of subquery) but the result differs from your example because your desired result doesn't correspond to your condition: a.start_date <= b.start_date (id 11 should have different b_id than id 22 and 33) Nov 13, 2020 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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