testing self join

projects-self-join.sql

CREATE TABLE projs (
  id serial PRIMARY KEY,
  parent_id integer,
  type text,
  proj_name text,
  proj_desc text,
  proj_loc text,
  proj_start date,
  proj_duration integer,
  attributes jsonb,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

INSERT INTO projs
  (parent_id, type, proj_name, proj_desc, proj_loc, proj_start, proj_duration)
VALUES
  (null, 'project', 'DMV', 'dmv bhs captures and sampling', 'statewide', '2015-01-01', 3),
  (null, 'project', 'Bat Blitz', 'project two stuff', 'statewide', '2015-01-01', 2),
  (null, 'project', 'Cherry Creek ekl', 'project three stuff', 'HU 121, 122, 123', '2015-10-01', 4),
  (1, 'stage', 'Muddy Mtns', 'stage 1 proj 1', 'muddy mtns', '2015-01-01', 1),
  (1, 'stage', 'Bare Mtns', 'stage 1 proj 2', 'bare mtns', '2016-01-01', 1),
  (1, 'stage', 'Snowstorm Mtns', 'stage 1 proj 3', 'snowstorms', '2017-01-01', 1);

SELECT p.id, p.type, p.proj_name, s.id, s.type, s.proj_name, s.parent_id
FROM projs p
  LEFT JOIN projs s ON p.id = s.parent_id
WHERE p.type = 'project'
ORDER BY p.id;