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.
Best Answer
So there are two ways to do this:
(targetId,start_date)
are unique, a correlated subquery will return consistent resultsIf a unique index exists:
No unique index exists:
For this I'm assuming
Id
onTable B
is a row identifier and unique. If it isn't, you will need to add additional columns to theSORT
portion of thePARTITION
statement to guarantee the query is deterministic.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 forIds
22, 33, 44.