CODEWITHSUNDEEP
postgresqlsubquery
Tobias
Tobias

Reputation: 2561

Getting a list value for every row (list of ids from another table)

I have a quite ordinary table with a foreign key to another table; for example:

CREATE TABLE table_a (
  id serial NOT NULL,
  name text,
  CONSTRAINT table_a_pkey PRIMARY KEY (id)
);
CREATE TABLE table_b (
  id serial NOT NULL,
  a_id integer,       -- The foreign key
  CONSTRAINT table_b_pkey PRIMARY KEY (id),
  CONSTRAINT table_b_a_id_fkey FOREIGN KEY (a_id)
      REFERENCES table_a (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
INSERT INTO table_a
     VALUES (1, 'First row');
-- 2 entries in table_b which refer to the existing table_a row:
INSERT INTO table_b
     VALUES (11, 1), (12, 1);

Now I'd like to have a view which gives me a list of all ids of table_b rows which refer to the current table_a row:

SELECT a.name,
       (SELECT b.id
          FROM table_b b
         WHERE b.id = a.id) AS b_ids
  FROM table_a a;

However, the b_ids column is empty; I'd like to have some kind of list there, containing the values 11 and 12.

Somewhere I read that subselects can only yield one column (ok for me in this case) and only one row (which would explain that the above query doesn't work for me). If this is true - how can this be done instead? Or do I really need to issue SELECT requests for every single table_a row in my program?

I'd like this to work with PostgreSQL 9.1 and 9.3.

Upvotes: 4

Views: 5457

Answers (2)

Marth
Marth

Reputation: 24812

You can use the array_agg function :

SELECT table_a.name, array_agg(table_b.id)
FROM table_a
LEFT OUTER JOIN table_b
ON table_a.id = table_b.a_id
GROUP BY table_a.name;

┌───────────┬───────────┐
│   name    │ array_agg │
├───────────┼───────────┤
│ First row │ {11,12}   │
└───────────┴───────────┘
(1 row)

Upvotes: 9

Vivek S.
Vivek S.

Reputation: 21905

select name
      ,string_agg(b.id::text,',') b_ids 
from table_a join table_b b on table_a.id= b.a_id
group by name

Upvotes: 0

Related Questions