Get data from 3 tables using UNION ALL

All we need is an easy explanation of the problem, so here it is.

I have these tables to which I am doing a query to get data from all the tables

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e8d847e118b372071e42c685e9e5ad72

In the sample, there is not a lot of data but I am looking for a way to paginate the data results of the UNION ALL query.

select
    C.EXID as CAMERA_ID,
    U.EMAIL,
    TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
    ARC.TITLE,
    ARC.EXID,
    ARC.CREATED_AT,
    ARC.FROM_DATE,
    ARC.TO_DATE,
    null as EMBED_CODE,
    ARC.FILE_NAME,
    ARC.FRAMES,
    ARC.URL,
    ARC.PUBLIC,
    ARC.STATUS,
    ARC.TYPE,
    null as EXTRA
from
    PUBLIC.ARCHIVES as ARC
left join USERS U on
    ARC.REQUESTED_BY = U.ID
left join CAMERAS C on
    ARC.CAMERA_ID = C.ID
union all
select
    C.EXID as CAMERA_ID,
    U.EMAIL,
    TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
    TL.TITLE,
    TL.EXID,
    TL.INSERTED_AT,
    TL.FROM_DATETIME,
    TL.TO_DATETIME,
    null as EMBED_CODE,
    null as FILE_NAME,
    null as FRAMES,
    null as URL,
    null as PUBLIC,
    TL.STATUS,
    null as type,
    TL.EXTRA
from
    PUBLIC.TIMELAPSES as TL
left join USERS U on
    TL.USER_ID = U.ID
left join CAMERAS C on
    TL.CAMERA_ID = C.ID
union all
select
    C.EXID as CAMERA_ID,
    U.EMAIL,
    TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
    COMP.EXID,
    COMP.NAME,
    COMP.INSERTED_AT,
    COMP.BEFORE_DATE,
    COMP.AFTER_DATE,
    COMP.EMBED_CODE,
    null as FILE_NAME,
    null as FRAMES,
    null as URL,
    COMP.PUBLIC,
    COMP.STATUS,
    null as type,
    null as EXTRA
from
    PUBLIC.COMPARES as COMP
left join USERS U on
    COMP.REQUESTED_BY = U.ID
left join CAMERAS C on
    COMP.CAMERA_ID = C.ID;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’m afraid that there’s no way that you can avoid using a UNION [ALL] in this case. Your timelapses, compares and archives tables are connected to users and to cameras but not to each other, so in order to link them, we have no choice but to use a UNION.

I played around a wee bit (with a subset of the fields) and I came up with this which may help (all the code below is in a fiddle here):

WITH core AS (
  SELECT 
    'Archives' AS tab_name, a.id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t)
SELECT * FROM core;

Result:

tab_name    id  exid    title   camera_id   requested_by
Archives    1   concre-r6ys     Concrete P  1   1
Archives    2   201809-27xn     2018-09-26  1   1
Archives    3   201902-htu9     2019-02-01  4   1
...
...
...
Archives    9   spark-kyzrq     Sparks Bui  1   1
Archives    10  201611-luw8     2016-11-06  2   4
Compares    1   compa-ylxwctg   o 29th Oct  1   2
Compares    2   test-hroqe      test        2   3
...
...

So, we can see that in the combined data, we have a way of getting at the source table name – by selecting it as a TEXT string at the beginning.

You can add in users info (like email, firstname, lastname) and cameras by JOINING to this "core" – see fiddle.

You can search now by doing something like this (again, see fiddle):

...
... core CTE query
...
)
SELECT * FROM core
WHERE title LIKE '%Pond%';

Result:

tab_name    tab_id         exid     title      camera_id    requested_by
Timelapse        1  longp-mvxla     Long Ponds         1               2

So, now we know that the photo with the title "Long Ponds" is in the Timelapse table and it’s id in that table is 1.

A few words of advice:

  • I would consolidate my (photograph) tables – this will make matters considerably easier. There should be some combination of fields that is UNIQUE – and you can keep the tab_name field (and use it to enforce uniqueness).

  • you have some field names (location, type…) as quoted identifiers. This should be avoided – use t_type… something with an underscore – which remains legible but isn’t an SQL keyword.

    If you decide to consolidate, you can have NULLs where there isn’t a corresponding field in the other tables.

  • a note on performance: As you can see from the EXPLAIN (ANALYZE, BUFFERS) at the end of the fiddle, your UNION plan has more than twice as many operations as my more_info table – this is because I perform the JOIN on users and cameras only once for all the photos whereas you do it three times.

    However you decide to do it – via sub-queries or CTEs, only doing it once is the better option. You could wrap your original UNIONs (without JOINs) in a SELECT and JOIN on that if you prefer… YMMV…

I know that this probably isn’t the outcome you hoped for, but I hope this has helped a little – best of luck with your project – agus slán go fóill a chara!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply