Creating an inverse of an Oracle view - EDITED

Question

I have two tables that I need to a create view which shows rows from the OBJECT table that don't appear in the OBJECT_VALUES table, for each month; OBJECT:

+------+--------+
| ID   | NAME   |
+------+--------+
| 1    | OBJ1   |
| 2    | OBJ2   |
| 3    | OBJ3   |
+---------------+

And OBJECT_VALUES:

+------+---------+---------+
| ID   |OBJECT_ID| MONTH   |
+------+---------+---------+
| 1    |    1    | 2016-4  |
| 2    |    3    | 2016-4  |
| 3    |    2    | 2016-5  |
| 4    |    3    | 2016-5  |
| 5    |    3    | 2016-6  |
| 6    |    1    | 2016-7  |
+------+---------+---------+

The expected results:

+---------+--------+
|OBJECT_ID| MONTH  |
+---------+--------+
| 1       | 2016-4 |
| 3       | 2016-4 |
| 2       | 2016-5 |
| 3       | 2016-5 |
| 3       | 2016-6 |
| 1       | 2016-7 |
+---------+--------+

What I am trying to achieve is the list of all OBJECTS per month that don't appear in the values for that month.

EDIT: I posted the wrong expected results, I apologize - I am trying to get the inverse of the above expected results which makes the following the expected results:

+---------+--------+
|OBJECT_ID| MONTH  |
+---------+--------+
| 2       | 2016-4 |
| 1       | 2016-5 |
| 1       | 2016-6 |
| 2       | 2016-6 |
| 2       | 2016-7 |
| 3       | 2016-7 |
+---------+--------+

Thank you in advance.

Answer 1

You asked for

rows from the OBJECT table that don't appear in the OBJECT_VALUES table, for each month

The following query produces that:

select      object.id as object_id,
            months.month
from        (select distinct month
             from   object_values) months
cross join  object
left join   object_values
         on object.id = object_values.object_id
        and months.month = object_values.month
where       object_values.id is null
order by    2, 1

The output is:

+-----------+--------+
| object_id |  month |
+-----------+--------+
|       2   | 2016-4 |
|       1   | 2016-5 |
|       1   | 2016-6 |
|       2   | 2016-6 |
|       2   | 2016-7 |
|       3   | 2016-7 |
+-----------+--------+

Note that this is not the output you presented in the question, but according to your question, this should be the output.

Alternative covering 12 months

This variation on the above SQL statement, uses a different query for the nested select in order to retrieve exactly the last 12 months:

select      object.id as object_id,
            months.month
from        (select to_char(add_months(trunc(sysdate, 'MONTH'), -level), 'YYYY-fmMM') 
                     as month
                from dual
                connect by level <= 12) months
cross join  object
left join   object_values
         on object.id = object_values.object_id
        and months.month = object_values.month
where       object_values.id is null
order by    2, 1;

It is a pity your month strings have the the month number in one digit, which means that they don't sort well. If ever you change your data to use 2 digits for all months, then replace the fmDD in the date format to DD.

Answer 2

select OBJECT_ID, MONTH from OBJECT_VALUES;