CODEWITHSUNDEEP
sqloracle-databasejoin
user1723699
user1723699

Reputation: 1091

Oracle SQL - Making a one to many join one to one based on logic

Sorry for the broad title, I had a hard time coming up with a brief way of describing what I am looking to do. I have two tables (examples below) that I want to join but under a certain condition.

The main table has a field called "DateVal", the second table has a field called "Day". After joining on field "JoinField" I only want to keep rows where the day value in "DateVal" is less than the value of "Day". However, if this criteria is met for multiple values of "Day" I only want it to keep the first instance.

In the second table below, for JoinField "A" there are three rows, for the first I only want it to return times when the day of the month is between 1-10, the second only with the day of the month is between 11-20, and the last 20-31.

A left or inner join will bring back all values, the only way I can think of to get around this is to do a complete join and only return for min("Day"). Can anyone think of a more efficient way?

Thanks in advance.

Table 1

-------------------------------
| ID | JoinField | DateVal    |
-------------------------------
| 1  | A         | 01/01/2014 |
| 2  | A         | 01/16/2014 |
| 3  | B         | 05/20/2013 |
-------------------------------

Table 2

--------------------------------
| JoinField | Day | FieldToAdd |
--------------------------------
| A         | 10  | A          |
| A         | 20  | AA         |
| A         | 31  | AAA        |
| B         | 15  | B          |
| B         | 31  | BB         |
--------------------------------

Desired Results

--------------------------------------------
| ID | JoinField | DateVal    | FieldToAdd |
--------------------------------------------
| 1  | A         | 01/01/2014 | A          |
| 2  | A         | 01/16/2014 | AA         |
| 3  | B         | 05/20/2014 | BB         |
--------------------------------------------

Upvotes: 0

Views: 1279

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You can do this in a variety of ways. I think a correlated subquery is the easiest way to express it, but unfortunately, the following doesn't work in Oracle:

select t1.*,
       (select *
        from (select t2.*
              from table2 t2
              where t2.day < extract(day from t1.dateval)
              order by t2.day desc
             ) t
        where rownum = 1
       )
from table1 t1;

You can instead do this with join fancy window functions:

select *
from (select t1.*,
             row_number() over (partition by t1.id order by t2.day desc) as seqnum
      from table1 t1 left outer join
           table2 t2
           on t2.day < extract(day from t1.dateval)
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions