Postgres select from one to many table to single table rows

Dejell

I have the following tables:

kid
  id
  name

kid_workshop
  id
  kid_id
  workshop_name

workshop_name could be only: arts, martial_arts, chess, soccer

in order to increase performance, I want to create a materialized view that will look like this:

kid_id   name   arts   martial_arts   chess   soccer
  1      Dann   True   True           False   True

How can I do it? I am using postgres

Tim Biegeleisen

Try the following query:

create materialized view your_view as 
select
    k.id,
    k.name,
    max(case when kw.workshop_name = 'arts'
             then 'true' else 'false' end) arts,
    max(case when kw.workshop_name = 'martial_arts'
             then 'true' else 'false' end) martial_arts,
    max(case when kw.workshop_name = 'chess'
             then 'true' else 'false' end) chess,
    max(case when kw.workshop_name = 'soccer'
             then 'true' else 'false' end) soccer
from kid k
inner join kid_workshop kw
    on k.id = kw.kid_id
group by k.id,
         k.name

This query employs a trick while doing the pivot. Specifically, it assigns the string 'true' for a positive case and 'false' to every negative case. Since 'true' is lexicographically greater than 'false', it should be retained using the MAX() function if present, otherwise false would be reported. This is precisely the logic we want.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

postgres sum different table columns from many to one joined data

From Dev

SQL One to many join - do not return rows from the 'one' table if ANY row in the 'many' table equals X

From Dev

Postgres Copy select rows from CSV table

From Dev

SELECT from single table one user per company

From Dev

How to select a single row where multiple rows exist from a table

From Dev

select multiple column from one table and insert into another as rows

From Dev

How to divide between rows in one table in Postgres

From Dev

Insert multiple rows with single a query from one table into another in Oracle

From Dev

Sqlalchemy single query for multiple rows from one column in one table

From Dev

How to select one to one relations from many to many table

From Dev

Select rows from one table and adjust the values based on rows in another table

From Dev

How can I select from many table to one table?

From Dev

Postgres Copy select rows from CSV table

From Dev

SELECT from single table one user per company

From Dev

Select many intervals from a single table based on column value

From Dev

How to select a single row where multiple rows exist from a table

From Dev

Select rows from a table if one of them matches with another table

From Dev

MYSQL Single query to retrieve both single row from one table and many rows as a single field from another

From Dev

How to select data from several rows in a single table in one result row?

From Dev

Select from one table with two foreign keys in a single query

From Dev

SQL - How to select discontinuous rows from a table with one select?

From Dev

Updating table from temp table with many rows in postgres

From Dev

Returning a single record from many to many table

From Dev

Insert many rows from a table into one unique row in another table

From Dev

Postgres SQL - Join data from 3 table to select one column

From Dev

Postgres : get min and max rows count in many to many relation table

From Dev

how to select one row from one table and multiple rows from other table using joins in mysql,

From Dev

How to SELECT none matching rows from one table to another?

From Dev

join single value from one table to multiple rows table - Oracle

Related Related

  1. 1

    postgres sum different table columns from many to one joined data

  2. 2

    SQL One to many join - do not return rows from the 'one' table if ANY row in the 'many' table equals X

  3. 3

    Postgres Copy select rows from CSV table

  4. 4

    SELECT from single table one user per company

  5. 5

    How to select a single row where multiple rows exist from a table

  6. 6

    select multiple column from one table and insert into another as rows

  7. 7

    How to divide between rows in one table in Postgres

  8. 8

    Insert multiple rows with single a query from one table into another in Oracle

  9. 9

    Sqlalchemy single query for multiple rows from one column in one table

  10. 10

    How to select one to one relations from many to many table

  11. 11

    Select rows from one table and adjust the values based on rows in another table

  12. 12

    How can I select from many table to one table?

  13. 13

    Postgres Copy select rows from CSV table

  14. 14

    SELECT from single table one user per company

  15. 15

    Select many intervals from a single table based on column value

  16. 16

    How to select a single row where multiple rows exist from a table

  17. 17

    Select rows from a table if one of them matches with another table

  18. 18

    MYSQL Single query to retrieve both single row from one table and many rows as a single field from another

  19. 19

    How to select data from several rows in a single table in one result row?

  20. 20

    Select from one table with two foreign keys in a single query

  21. 21

    SQL - How to select discontinuous rows from a table with one select?

  22. 22

    Updating table from temp table with many rows in postgres

  23. 23

    Returning a single record from many to many table

  24. 24

    Insert many rows from a table into one unique row in another table

  25. 25

    Postgres SQL - Join data from 3 table to select one column

  26. 26

    Postgres : get min and max rows count in many to many relation table

  27. 27

    how to select one row from one table and multiple rows from other table using joins in mysql,

  28. 28

    How to SELECT none matching rows from one table to another?

  29. 29

    join single value from one table to multiple rows table - Oracle

HotTag

Archive