CODEWITHSUNDEEP
sqloracle-database
user3286565
user3286565

Reputation: 29

Join from table a single date column with another tables date range oracle 11 g

I'm trying to build a query from an Oracle 11g database to use in a report. I need to use two tables A and B to get the Table A column date and Table column B Value

Table A :

Dt
01/01/2014
02/01/2014
03/01/2014
04/01/2014
05/01/2014
06/01/2014

TABLE B:

Value      Start Dt     End Dt
XXXX       01/01/2014    03/01/2014
YYYY       05/01/2014    06/01/2014

Final Output:

A.Dt               B.Value
01/01/2014         XXXX
02/01/2014         XXXX
03/01/2014         XXXX
04/01/2014         NULL
05/01/2014         YYYY
06/01/2014         YYYY

Please help to do above in a single sql query.

Thanks

Upvotes: 3

Views: 4583

Answers (2)

user3274103
user3274103

Reputation: 23

    SELECT A.DT ,B.VALUE

FROM A

LEFT OUTER JOIN

(

SELECT START_DT,VALUE FROM B

UNION 

SELECT END_DT,VALUE FROM B

) B

ON A.DT=B.START_DT

ORDER BY A.DT

This should give you the intended output.

Upvotes: 0

Mureinik
Mureinik

Reputation: 312219

Not all joins are equijoins - you can use the BETWEEN operator in your join condition.

SELECT          a.dt, b.value
FROM            a
LEFT OUTER JOIN b ON a.dt BETWEEN b.start_dt and b.end_dt

Upvotes: 1

Related Questions