Where could something like this come in handy ?

I used it to display all categories in one go for a particuar item.

To show that test we need three tables:

create table item (
item_id serial not null primary key,
item_name varchar(100)
);

create table category (
category_id serial not null primary key,
category_name varchar(100)
);

create table item_categories (
item_id int references item(item_id),
category_id int references category(category_id)
);

-- create our item
insert into item(item_name) values ('item 1');

-- create our categories
insert into category(category_name) values ('category 1');
insert into category(category_name) values ('category 2');

-- create our 'associations' between the two
-- 'item 1' is in both categories
insert into item_categories(item_id, category_id) values (1,1);
insert into item_categories(item_id, category_id) values (1,2);

Normally we have to handle duplicates being displayed in code, because for each item to category association, each part will be displayed:


SELECT
i.item_id, i.item_name, c.category_id, c.category_name
FROM
item i
INNER JOIN item_categories ic USING (item_id)
INNER JOIN category c USING (category_id);

 item_id | item_name | category_id | category_name

---------+-----------+-------------+---------------

       1 | item 1    |           1 | category 1

       1 | item 1    |           2 | category 2

(2 rows)

The query we end up with is:

select item_name,
array_to_string(
array(
SELECT category_name FROM category c INNER JOIN item_categories ic USING (category_id) WHERE ic.item_id=i.item_id
),','
) AS "category_list"
FROM
item i;


The subquery returns each category that the item is associated with, which is then turned into an array (then into a string).

It uses the outer table ('item') to restrict the categories it is retrieving to make sure it only retrieves the rows we need.

The end result is:

 item_name |     category_list

-----------+-----------------------

 item 1    | category 1,category 2

(1 row)

Perfect!