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

-- attempted query

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

SELECT items.id, title, industries.name, categories.name
FROM items INNER JOIN items_industries ON items.id = items_industries.item INNER JOIN industries ON industries.id = industry
INNER JOIN items_categories ON items.id = items_categories.item INNER JOIN categories ON categories.id = category
WHERE items.id = 31;

SELECT * FROM items_categories 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) AS foo) ON items_categories.item = foo.item_id;