link tree and related table

recursive.sql

I have 2 tables. 

category and products. [from google product](https://www.google.com/basepages/producttype/taxonomy.en-US.txt) taxonomy tree i was able to reconstruct the entire tree.

i have the following tables, 

```
CREATE TABLE public.category
(
  id integer NOT NULL DEFAULT nextval('category_id_seq'::regclass),
  name character varying(400),
  image text,
  rgt integer NOT NULL,
  parent_id integer,
  level integer NOT NULL,
  tree_id integer,
  lft integer NOT NULL,
  CONSTRAINT category_pkey PRIMARY KEY (id),
  CONSTRAINT category_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES public.category (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
```

and the following `catalog` table, 



```
CREATE TABLE public.catalog
(
  id integer NOT NULL DEFAULT nextval('catalog_id_seq'::regclass),
  name character varying(300),
  category_id integer,
  description text,
  CONSTRAINT catalog_pkey PRIMARY KEY (id),
  CONSTRAINT catalog_category_id_fkey FOREIGN KEY (category_id)
      REFERENCES public.category (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT catalog_color_id_fkey FOREIGN KEY (color_id)
      REFERENCES public.color (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT catalog_size_id_fkey FOREIGN KEY (size_id)
      REFERENCES public.size (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.catalog
  OWNER TO ladies;
```



```

WITH RECURSIVE catree AS
(SELECT id, name, parent_id, CAST(name As varchar(1000)) As cat_fullname
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT cat.id,cat.name,
	cat.parent_id,
	CAST(categ.cat_fullname || '->' || cat.name As varchar(1000)) As cat_fullname
FROM category As cat
	INNER JOIN catree AS categ
	ON (cat.parent_id = categ.id)
)
SELECT id, cat_fullname
FROM catree
ORDER BY cat_fullname;
```



how do i list category trees if only the child's category.id is == catalog.category_id ?