POSTGRES: INNER JOIN 1:N with multiple tables. Need to count and display records that were accessed as a list

Asked

Viewed 123 times

0

I need to count table records acesso_registro and show in a list.

Schema:

acesso_registro
+--------------+--------+------------+
|id_tipo_midia |id_midia| data_acesso| 
+--------------+--------+------------+
|01            | 03     | 2019-06-03 |
|01            | 03     | 2019-06-03 |
|03            | 01     | 2019-06-05 |
|02            | 03     | 2019-06-06 |
|02            | 01     | 2019-06-06 |
|03            | 02     | 2019-07-08 |
+--------------+--------+------------+

tipo_midia
+--+-------+
|id| tipo  |
+----------+
|01| Vídeo |
|02| Audio |
|03| Imagem|
|04| Outro |
+----------+

videos
+--+----------+
|id| titulo   |
+-------------+
|01| video_01 |
|02| video_02 |
|03| video_03 |
|04| video_04 |
+-------------+

audios
+--+----------+
|id| titulo   |
+-------------+
|01| audio_01 |
|02| audio_02 |
|03| audio_03 |
|04| audio_04 |
+-------------+

imagens
+--+-----------+
|id| titulo   |
+--------------+
|01| imagem_01 |
|02| imagem_02 |
|03| imagem_03 |
|04| imagem_04 |
+--------------+

The query must return me:

*---------*---------*--------*
|tipoMidia| titulo  | acessos|
*---------*---------*--------*
|vídeo    |video_03 | 2      |
|Imagem   |imagem_01| 1      |
|Imagem   |imagem_02| 1      |
|Audio    |audio_01 | 1      |
|Audio    |audio_03 | 1      |
*---------*---------*--------*

Note: The table acesso_registro increments a line whenever a media is accessed. The database I use is Postgres.

postgresql query inner-join

  • You mean you will decide which table to merge with according to the contents of the id_typo_midia field of the access table_record?

  • Yes! See that in the table acesso_registro, the first two lines show that there are two records for the media type "Video" table tipo_midia and that these two records are the id 03 of the table videos, i.e., the "video_03".

  • To simplify if you can complicate!

1 answer

2


Try using UNION for the junctions of each media type:

SELECT tipoMidia, titulo, acessos FROM (
    (SELECT  tipo_midia.tipo, videos.titulo, COUNT(acesso_registro.data_acesso) AS acessos
            FROM acesso_registro INNER JOIN tipo_midia ON (acesso_registro.id_tipo_midia = tipo_midia.id) 
                                        INNER JOIN videos ON (acesso_registro.id_midia = videos.id)
            WHERE tipo_midia.tipo = 'video'
            GROUP BY 1, 2)
    UNION
    (SELECT  tipo_midia.tipo, audios.titulo, COUNT(acesso_registro.data_acesso) AS acessos 
            FROM acesso_registro INNER JOIN tipo_midia ON (acesso_registro.id_tipo_midia = tipo_midia.id) 
                                        INNER JOIN audios ON (acesso_registro.id_midia = audios.id)
            WHERE tipo_midia.tipo = 'audio'
            GROUP BY 1, 2)
    UNION
    (SELECT  tipo_midia.tipo, imagens.titulo, COUNT(acesso_registro.data_acesso) AS acessos 
            FROM acesso_registro INNER JOIN tipo_midia ON (acesso_registro.id_tipo_midia = tipo_midia.id) 
                                        INNER JOIN imagens ON (acesso_registro.id_midia = imagens.id)
            WHERE tipo_midia.tipo = 'imagem'
            GROUP BY 1, 2)
);

Disregarding all where media type is 'other'.

Browser other questions tagged postgresql query inner-join

You are not signed in. Login or sign up in order to post.