Nkuruza Nkuruza - 3 months ago 12
SQL Question

Creating an inverse of an Oracle view - EDITED

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

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.

Comments