Join two tables and merge multiple associations in one row

Question

I'm currently stuck when joining two PSQL tables. I know these kind of questions are asked all the time, I could however not find an answer applicable to my situation. I apologise for the vague question title.

To describe the simplified situation: We've got two tables, children and parents. As you can see the children are linked to their parents with their ids in a comma separated 'array'. I want to create a simple view (non materialized) where parents are joined with their children, from the children's perspective.

table children

| id | name  | parents |
| -- | ----- | ------- |
| 1  | Bob   | 1,2     |
| 2  | Alice | 3       |

table parents

| id | name  | phone |
| -- | ----- | ----- |
| 1  | Carol | 1234  |
| 2  | Frank | 5678  |
| 3  | Grace | 9012  |

desired combined view

| child_id | child_name | parent1_name | parent1_phone | parent2_name | parent2_phone |
| -------- | ---------- | ------------ | ------------- | ------------ | ------------- |
| 1        | Bob        | Carol        | 1234          | Frank        | 5678          |
| 2        | Alice      | Grace        | 9012          |              |               |

---

I've tried to achieve the above with the following view definition:

SELECT children.id AS child_id,
    children.name AS child_name,
    parents.name AS parent_name,
    parents.phone AS parent_phone
   FROM children
     JOIN parents ON parents.id::text = 
       ANY (string_to_array(children.parents, ','::text));

This of course does not merge the two parents of a child into a single row:

| child_id | child_name | parent_name | parent_phone |
| 1        | Bob        | Carol       | 1234         |
| 1        | Bob        | Frank       | 5678         |
| 2        | Alice      | Grace       | 9012         |

What is the best solution to my desired result?

Answer 1

You need create some aggregation after your current query

SQL DEMO

WITH cte as (
    SELECT *, row_number() over (partition by child_id order by "parent_name") as rn
    FROM queryResult
)
SELECT child_id, 
       child_name, 
       MAX( CASE WHEN rn = 1 then parent_name END) as parent_name_1,
       MAX( CASE WHEN rn = 1 then parent_phone END) as parent_phone_1,
       MAX( CASE WHEN rn = 2 then parent_name END) as parent_name_2,
       MAX( CASE WHEN rn = 2 then parent_phone END) as parent_phone_2
FROM cte
GROUP BY child_id, child_name
ORDER BY child_id

OUTPUT

NOTE: This assume at most a child have only 2 parents. So be careful if you oversimplify your question or have modern family in your data.