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)
);

-- rewrite

SELECT 
    it.id AS item_id, it.title, gi.industries, gc.categories 
FROM 
    items AS it 
  LEFT JOIN 
    ( SELECT ii.item AS item_id, array_agg(i.name) AS industries
      FROM items_industries AS ii  
        INNER JOIN industries AS i
          ON i.id = ii.industry
      WHERE ii.item  = 31 
      GROUP BY ii.item 
    ) AS gi ON gi.item_id = it.id 
  LEFT JOIN 
    ( SELECT ic.item AS item_id, array_agg(c.name) AS categories
      FROM items_categories AS ic 
        INNER JOIN categories AS c
          ON c.id = ic.category
      WHERE ic.item  = 31 
      GROUP BY ic.item 
    ) AS gc ON gc.item_id = it.id 
WHERE 
    it.id = 31 ;


-- rewrite 2

SELECT 
    it.id AS item_id, it.title, 
    array_agg(DISTINCT i.name) AS industries, 
    array_agg(DISTINCT c.name) AS categories 
FROM 
    items AS it 
  LEFT JOIN 
    items_industries AS ii 
      ON ii.item_id = it.id 
  LEFT JOIN 
    industries AS i
      ON i.id = ii.industry
  LEFT JOIN 
    items_categories AS ic 
      ON ic.item_id = it.id 
  LEFT JOIN
    categories AS c
     ON c.id = ic.category
WHERE 
    it.id = 31 
GROUP BY
    it.id ;