Use conditional to check dates if not in a search column with null value

Question

I have the following tables in BD Oracle 11g

irrigation
----------------
militet_applied, 
close_date

rainfall
---------------
rain_index, 
reading_date

And I would like to make a select similar to that:

select r.rain_index, 
       i.militet_applied
from irrigation i, rainfall r

But in my where clause I would like to do the following: - If r.reading_date = i.close_date bring me the values (i.militet_applied), but also if r.reading_date be equal/not present in the column i.close_cycle, bring me null values (i.militet_applied).

I’m counting on you in this doubt.

Answer 1

Josefosad, although the question is confused, following exactly what you reported, one option is this:

SELECT 
  R.RAIN_INDEX, 
  CASE 
    WHEN R.READING_DATE = I.CLOSE_CYCLE OR R.READING_DATE NOT IN (SELECT I2.CLOSE_CYCLE FROM IRRIGATION I2) THEN NULL
    ELSE I.MILITET_APPLIED END AS MILITET_APPLIED
FROM 
  RAINFALL R
  JOIN IRRIGATION I ON R.READING_DATE = I.CLOSE_DATE;

If you want the column MILITET_APPLIED to be null only when there is no match at the junction (R.READING_DATE = I.CLOSE_DATE), use LEFT JOIN:

SELECT 
  R.RAIN_INDEX, 
  I.MILITET_APPLIED
FROM 
  RAINFALL R
  LEFT JOIN IRRIGATION I ON R.READING_DATE = I.CLOSE_DATE;