How to use a subquery in array_agg without using a join [closed]

I have a query that I would like to write wihout using a join and concat all phone numberes together from another table. The phone numbers are linked together by ink_id field.

I tried these query but both of them are giving me a syntax error.

select
    ink.fullname,
    array_agg(select phone_number from phone_numbers where phone_numbers.ink_id = ink.id) as _phones
from
    ink

select
    ink.fullname,
    select(array_agg(phone_number) from phone_numbers where phone_numbers.ink_id = ink.id) as _phones
from
    ink

Ink table

+-----+------------+
| id  |  fullname  |
+-----+------------+
| 567 | John Smith |
| 159 | Caleb Doe  |
| 333 | Bill Gates |
+-----+------------+

Phone number table

+----+--------+--------------+
| id | ink_id | phone_number |
+----+--------+--------------+
|  1 |    333 |    516519899 |
|  2 |    159 |    216584989 |
|  3 |    333 |    123149849 |
+----+--------+--------------+

So the result should be

+-----+------------+----------------------+
| id  |  fullname  |    _phone_numbers    |
+-----+------------+----------------------+
| 567 | John Smith |                      |
| 159 | Caleb Doe  | 216584989            |
| 333 | Bill Gates | 516519899, 123149849 |
+-----+------------+----------------------+

Answer

The second method would work, if you used the right syntax:

select ink.fullname,
       (select array_agg(pn.phone_number)
        from phone_numbers pn
        where pn.ink_id = ink.id
       ) as _phones
from ink;

Subqueries need to be surrounded by parentheses.

Advertisement

Answer