setup.sql

CREATE TABLE IF NOT EXISTS items (
  id serial PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS industries (
  id serial PRIMARY KEY,
  name text UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS items_industries (
  item integer REFERENCES items(id),
  industry integer REFERENCES industries(id)
);

CREATE TABLE IF NOT EXISTS categories (
  id serial PRIMARY KEY,
  name text UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS items_categories (
  item integer REFERENCES items(id),
  industry integer REFERENCES industries(id)
);

-- now-functioning query

SELECT item_id, title, industries, array_agg(name)
FROM
  items_categories ic
  INNER JOIN (SELECT items.id as item_id, title, array_agg(industries.name) as industries FROM
      items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
    WHERE items.id = 31 GROUP BY items.id) jt
  ON (ic.item = jt.item_id)
INNER JOIN categories ON category = categories.id GROUP BY item_id, title, industries;