SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Categories (
  `id` INTEGER,
  `name` VARCHAR(6),
  `description` VARCHAR(31)
);
INSERT INTO Categories
  (`id`, `name`, `description`)
VALUES
  ('1', 'wet', 'something associated with water'),
  ('2', 'dry', 'something else'),
  ('3', 'metal', 'steel, copper');
CREATE TABLE Items (
  `id` INTEGER,
  `name` VARCHAR(6),
  `status` VARCHAR(10)
);
INSERT INTO Items
  (`id`, `name`, `status`)
VALUES
  ('11', 'river', 'north fork'),
  ('12', 'lake', 'big'),
  ('13', 'river', 'south fork'),
  ('14', 'desert', 'mojave'),
  ('15', 'car', 'ford'),
  ('16', 'truck', 'chevy');
CREATE TABLE Reference (
  `id` INTEGER,
  `cat_id` INTEGER,
  `item_id` INTEGER
);
INSERT INTO Reference
  (`id`, `cat_id`, `item_id`)
VALUES
  ('21', '1', '11'),
  ('22', '1', '12'),
  ('23', '2', '14'),
  ('24', '3', '15'),
  ('25', '3', '16');
SELECT c.name,
       GROUP_CONCAT(i.name || ' - ' || i.status, ', ') Related
FROM Categories c
LEFT JOIN Reference r ON r.cat_id = c.id
LEFT JOIN Items i ON i.id = r.item_id
GROUP BY c.id, c.name


name
Related


wet
river - north fork, lake - big

dry
desert - mojave

metal
car - ford, truck - chevy

name	Related
wet	river - north fork, lake - big
dry	desert - mojave
metal	car - ford, truck - chevy