One-to-many relationship on same table, how to return each sub level?

Question

I have a question about a one-to-many relationship on the same table.

So I have this two tables:

CREATE TABLE IF NOT EXISTS public.djangoadmin_animal
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    slug character varying(100) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    images character varying(100) COLLATE pg_catalog."default" NOT NULL,
    date_create timestamp with time zone NOT NULL,
    date_update timestamp with time zone NOT NULL,
    category_id bigint NOT NULL,
    CONSTRAINT djangoadmin_animal_pkey PRIMARY KEY (id),
    CONSTRAINT djangoadmin_animal_category_id_3d95d2d9_fk_djangoadm FOREIGN KEY (category_id)
        REFERENCES public.djangoadmin_category (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

CREATE TABLE IF NOT EXISTS public.djangoadmin_category
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    slug character varying(100) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    images character varying(100) COLLATE pg_catalog."default" NOT NULL,
    date_create timestamp with time zone NOT NULL,
    date_update timestamp with time zone NOT NULL,
    category_id bigint,
    CONSTRAINT djangoadmin_category_pkey PRIMARY KEY (id),
    CONSTRAINT djangoadmin_category_category_id_9b327c27_fk_djangoadm FOREIGN KEY (category_id)
        REFERENCES public.djangoadmin_category (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

And this is some example data from the table public.djangoadmin_category:

11  "zoogdieren"    "zoogdieren"    "hoi"   "photos/categories/1_eDJtmdP.jpg"   "2023-01-27 18:25:18.624272+01" "2023-01-27 18:25:18.624272+01" 
12  "amfibieen" "amfibieen" "kujhkjh"   "photos/categories/1_KJDTBPc.jpg"   "2023-01-27 18:25:38.444066+01" "2023-01-27 18:25:38.444066+01" 
13  "vogels"    "vogels"    "kljhkjh"   "photos/categories/1_FGkA44b.jpg"   "2023-01-27 18:26:00.390812+01" "2023-01-27 18:26:00.390812+01" 
16  "roofvogels"    "roofvogels"    "kljhkljjl" "photos/categories/1_pA0TNrX.jpg"   "2023-01-27 18:29:16.101478+01" "2023-01-27 18:29:16.102479+01" 13
17  "kikkers"   "kikkers"   "kjhkjh"    "photos/categories/1_zk2WQLP.jpg"   "2023-01-27 18:29:44.073516+01" "2023-01-27 18:29:44.073516+01" 12
21  "reptielen" "reptielen" "reptielen" "photos/categories/1_EoVggfL.jpg"   "2023-01-27 18:55:04.565339+01" "2023-01-27 18:55:04.565339+01" 
22  "slangen"   "slangen"   "slangen"   "photos/categories/1_w4pzls7.jpg"   "2023-01-27 18:55:23.181336+01" "2023-01-27 18:55:23.181336+01" 21
23  "schildpadden"  "schildpadden"  "schildpadden"  "photos/categories/1_RkKQ5md.jpg"   "2023-01-27 18:55:51.724641+01" "2023-01-27 18:55:51.724641+01" 
24  "honden"    "ohhhh" "hhhh"  "photos/categories/1_iUcB8K5.jpg"   "2023-01-27 19:24:35.589541+01" "2023-01-27 19:24:35.590538+01" 11
25  "katten"    "kjhkjh"    "kjhkjh"    "photos/categories/1_5LxINWC.jpg"   "2023-01-27 19:24:48.07098+01"  "2023-01-27 19:24:48.07098+01"  11
26  "olifanten" "olifanten" "kjhkjhkjh" "photos/categories/1_kmRFovt.jpg"   "2023-01-27 19:25:05.648655+01" "2023-01-27 19:25:05.648655+01" 11
27  "krokodillen"   "krokodillen"   "l;l;'ll;;'l"   "photos/categories/1_YuCHCct.jpg"   "2023-01-27 19:27:18.06109+01"  "2023-01-27 19:27:18.06109+01"  12

and here is some explanation:

So there are main groups:

zoogdieren(mammals)
amfibieen(amphibians)
vogels(birds)

and you have a category:

honden(dogs)
katten(cats)

and they belong to the main group: zoogdieren(mammals)

My concrete question is:

Is it possible to write a query with this relationship to get all the main groups and to get all the categories?

So the query for main group will be as output:

zoogdieren(mammals)
amfibieen(amphibians)
vogels(birds)

and query for example zoogdieren(mammals) will be:

honden(dogs)
katten(cats)

I think this is not possible. Or is it? I try it like this:

SELECT
    A.name, A.category_id, B.name, B.category_id
FROM 
    djangoadmin_category A, djangoadmin_category B
WHERE 
    A.category_id = B.category_id
    AND A.name = B.name
ORDER BY 
    A."name"

Does this answer your question? [How to create a MySQL hierarchical recursive query?](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — philipxy, Jan 28 '23 at 11:32
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (more than **30 years!!** ago) and its use is discouraged — marc_s, Jan 28 '23 at 12:43

score 0 · Answer 1 · answered Jan 28 '23 at 11:36

0

If I understand you correctly. You have this DB structure

Animal(Honden) --> Category(Dog) --> Category (Mammal) which is just a hierarchy of categories in one table and animals in another table belonging to a category anywhere in the hierarchy

What you present is a very simple output, is that the output you want or do you want an output to walk up the hierarchy to the top of the tree?

What is the final output you want, it looks like the output you want is Animal --> Category which is a simple join. Walking up the tree to find the major category is a bit more complicated, but very simple in most SQL Engines. The different engines have different SQLs for walking up or down trees. Lookup CONNECT BY JOIN for Oracle or Common Table Expression for PostgreSQL for clues

answered Jan 28 '23 at 11:36

Touring Tim

49
4

#Touring Tim.Yes, I am using postgress. A user fills in the main groups, categories. So for example all the main groups have to be returned. But also the categories under the main group have to be returned. So if a user selects main group: mammals then all the categegories that are related with mammals have to be returned - so in this case: cats and dogs. – mightycode Newton Jan 28 '23 at 11:43
Of course: The relationship between category and animal is simple. But it is now about the table category. How to return the main groups with the underlying items and how to return the categories with the underlying items? Is this even possible with this strcuture? – mightycode Newton Jan 28 '23 at 11:52

One-to-many relationship on same table, how to return each sub level?

1 Answers1