Agregate data from different tables

Question

I'm stuck at trying something.

As you can see below, I have those tables (Application, Version, Record, record_version_application, History)

Record table contain every active records. History contains non active records and it keep an history of records that has been edited.

I'm working with Postgresql 10

Table Application & Version:

+---------------------+-----------------------------+-----------------------+
|  Table Application  |                             |     Table version     |
+---------------------+-----------------------------+-----------------------+
| ip_app | nom_app    |                             | id_version | version  |
+--------+------------+-----------------------------+------------+----------+
| 10     | ABC        |                             | 4          | 1.0.0.1  |
+--------+------------+-----------------------------+------------+----------+
| 13     | DEF        |                             | 5          | 1.2.0.1  |
+--------+------------+-----------------------------+------------+----------+
| 20     | GHI        |                             | 6          | 1.0.0.1  |
+--------+------------+-----------------------------+------------+----------+
| 28     | JKL        |                             | 7          | 2.2.2.2  |
+--------+------------+-----------------------------+------------+----------+
|        |            |                             | 8          | 2.2.2.3  |
+--------+------------+-----------------------------+------------+----------+
|        |            |                             | 9          | 1.1.1.1  |
+--------+------------+-----------------------------+------------+----------+

Table Record:

+-----------+------------+-----------+
| id_record | start_date | end_date  |
+-----------+------------+-----------+
| 5         | 2018-7-10  | 2018-7-12 |
+-----------+------------+-----------+
| 9         | 2018-7-15  | 2018-7-18 |
+-----------+------------+-----------+
| 10        | 2018-7-20  | 2018-7-30 |
+-----------+------------+-----------+

Table record_version_application

+-----------+------------+----------------+
| id_record | id_version | id_application |
+-----------+------------+----------------+
| 5         | 1.0.0.1    | 4              |
+-----------+------------+----------------+
| 9         | 1.2.0.1    | 8              |
+-----------+------------+----------------+
| 10        | 1.0.0.1    | 9              |
+-----------+------------+----------------+

Table History:

+------------+-----------+--------+---------+------------+-----------+
| id_history | id_record | id_app | version | start_date | end_date  |
+------------+-----------+--------+---------+------------+-----------+
| 9          | 2         | 10     | 1.0.0.0 | 2017-6-25  | 2017-7-30 |
+------------+-----------+--------+---------+------------+-----------+
| 10         | 5         | 10     | 1.0.0.1 | 2018-7-15  | 2018-7-10 |
+------------+-----------+--------+---------+------------+-----------+
| 11         | 10        | 20     | 1.2.0.1 | 2018-7-18  | 2018-7-28 |
+------------+-----------+--------+---------+------------+-----------+
| 12         | 1         | 13     | 2.2.2.2 | 2018-5-10  | 2018-5-16 |
+------------+-----------+--------+---------+------------+-----------+
| 13         | 9         | 13     | 2.2.2.3 | 2018-7-5   | 2018-7-8  |
+------------+-----------+--------+---------+------------+-----------+
| 14         | 9         | 13     | 2.2.2.3 | 2018-7-12  | 2018-7-18 |
+------------+-----------+--------+---------+------------+-----------+
| 15         | 3         | 28     | 1.1.1.1 | 2018-7-12  | 2018-7-15 |
+------------+-----------+--------+---------+------------+-----------+
| 16         | 3         | 28     | 1.1.1.1 | 2018-8-12  | 2018-8-20 |
+------------+-----------+--------+---------+------------+-----------+

The first extract result is supposed to be a UNION query between the two tables. If a [id_app/version] couple exists in both table, it should only keep the one in the record table.

If there's many identical [id_app/version] couple in the History table, it should only keep the last one (which is also supposed to be the last one inserted in the DB).

First extract result would be something like this:

+--------+---------+------------+-----------+
| id_app | version | start_date | end_date  |
+--------+---------+------------+-----------+
| 10     | 1.0.0.0 | 2017-6-25  | 2017-7-30 |
+--------+---------+------------+-----------+
| 10     | 1.0.0.1 | 2018-7-10  | 2018-7-12 |
+--------+---------+------------+-----------+
| 20     | 1.2.0.1 | 2018-7-20  | 2018-7-30 |
+--------+---------+------------+-----------+
| 13     | 2.2.2.2 | 2018-5-10  | 2018-5-16 |
+--------+---------+------------+-----------+
| 13     | 2.2.2.3 | 2018-7-15  | 2018-7-18 |
+--------+---------+------------+-----------+
| 28     | 1.1.1.1 | 2018-8-12  | 2018-8-20 |
+--------+---------+------------+-----------+

Final result is supposed to be the aggregated result from the previous result using array_agg (Postgresql) GROUPED BY the id_app

And final desired result would be:

+--------+--------------------+------------------------+------------------------+
| name_app| version            | start_date             | end_date               |
+--------+--------------------+------------------------+------------------------+
| ABC    | [1.0.0.0, 1.0.0.1] | [2017-6-25, 2018-7-10] | [2017-7-30, 2018-7-12] |
+--------+--------------------+------------------------+------------------------+
| GHI    | [1.2.0.1]          | [2018-7-18]            | [2018-7-28]            |
+--------+--------------------+------------------------+------------------------+
| DEF    | [2.2.2.2, 2.2.2.3] | [2018-5-10, 2018-7-15] | [2018-5-16, 2018-7-18] |
+--------+--------------------+------------------------+------------------------+
| JKL    | [1.1.1.1]          | [2018-8-12]            | [2018-8-20]            |
+--------+--------------------+------------------------+------------------------+

Note that start_date, end_date can also be NULL

I've been able to realize only a part of this which only rely on Record table and not the history

SELECT a.name_app, array_to_json(array_agg(v.version ORDER BY version)) AS version, 
array_to_json(array_agg(to_char(start_date, 'DD Mon YYYY') ORDER BY version)) AS start_date, 
array_to_json(array_agg(to_char(end_date, 'DD Mon YYYY') ORDER BY version)) AS end_date
        FROM record r NATURAL JOIN record_application_version NATURAL JOIN version v NATURAL JOIN application a
        GROUP BY nom_app
        ORDER BY nom_app;

I'm stuck at doing this with both Record and History table.

I've been using and trying so far with this:

• Optimize GROUP BY query to retrieve latest record per user

• MySQL Select rows on first occurrence of each unique value

• Selecting rows ordered by some column and distinct on another

• PostgreSQL DISTINCT ON with different ORDER BY

If you have any tips that could help me to achieve this I would be grateful. Moreover, if you have any other advice I'll take them gladly.

Thank you

PS: English isn't my mother tongue. If something isn't clear tell me, I'll try to make it clear.

Answer 1

To construct a union C between two tables A and B in which C contain every rows of A and rows of B that aren't in A.

Having A a JOIN table of record, version, application, record_application_version and B the history table.

I've solved my problem with a WHERE NOT EXISTS query like this:

SELECT * FROM A
UNION
SELECT * FROM B R1
    WHERE NOT EXISTS
        SELECT * FROM A
            WHERE A.value = B.value;
    AND NOT EXISTS 
    (SELECT * FROM R2
         WHERE R1.date_value < R2.date_value AND R1.id_app = R2.id_app);