2021-01-31

PostgreSQL LEFT JOIN with all associated right-side items returned in an array

I have a table tbl_a.

 id | name
----+-------
  1 | item1
  2 | item2
  3 | item3

I also have a table tbl_aprops which stores properties prop associated with items a from tbl_a.

 a_id | prop
------+------
    1 | xx
    1 | yy
    2 | xx
    2 | zz
    3 | yy

I wish to perform a LEFT JOIN where all the information from the left table tbl_a is returned along with the associated properties prop from table_aprops in an array in a new column called props. I wish to filter out items not associated with property yy while still returning all associated properties prop from tbl_aprops.

 id | name  |   props
----+-------+-----------
  1 | item1 | {'xx','yy'}
  3 | item3 | {'yy'}


from Recent Questions - Stack Overflow https://ift.tt/36JcghN
https://ift.tt/eA8V8J

No comments:

Post a Comment