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.
x
select
ink.fullname,
array_agg(select phone_number from phone_numbers where phone_numbers.ink_id = ink.id) as _phones
from
ink
or
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 |
+-----+------------+----------------------+
Advertisement
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.