Select one to many parent-child and many to many table-CodePudding

I have 3 tables: category, subcategory and skill.

A subcategory can belong only to one category and can have any skill. A skill can belong to any subcategory.

Category-subcategory is one to many, subcategory-skill is many to many.

I need to show all the subcategories from one category, and all the skills for these subcategories. I don't know how to do it. Here are my tables:

CREATE TABLE Skills_Category (
    categoryId int(4) AUTO_INCREMENT NOT NULL, 
    name varchar(150) NOT NULL, 
    PRIMARY KEY (categoryId)
);
CREATE TABLE Skills_Subcategory (
    subcategoryId int(4) AUTO_INCREMENT NOT NULL,
    name varchar(150) NOT NULL,
    categoryId int(4) NOT NULL,
    PRIMARY KEY (subcategoryId),
    CONSTRAINT Constr_Skills_Subcategory_Subcategory_fk FOREIGN KEY Subcategory_fk (categoryId) REFERENCES Skills_Category(categoryId)
);
CREATE TABLE Skills (
    skillId int(4) AUTO_INCREMENT NOT NULL,
    name varchar(150) NOT NULL,
    PRIMARY KEY (skillId)
);

CREATE TABLE Skills_rel_Subcategory_Skill (
    Subcategory INT NOT NULL, 
    Skill INT NOT NULL, 
    PRIMARY KEY (Subcategory, Skill), 
    CONSTRAINT Constr_Skills_rel_Subcategory_Skill_Subcategory_fk FOREIGN KEY Subcategory_fk (Subcategory) REFERENCES Skills_Subcategory(subcategoryId) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT Constr_Skills_rel_Subcategory_Skill_Skill_fk FOREIGN KEY Skill_fk (Skill) REFERENCES Skills_Skill(skillId) ON DELETE CASCADE ON UPDATE CASCADE
);

Here's how I can select the category and subcategory, and then how I can select the subcategory and the skill. I need to do both with the same query:

SELECT cat.*, GROUP_CONCAT(sc.name) as subcategory 
    FROM Skills_Category AS cat 
        JOIN Skills_Subcategory AS sc 
        ON sc.categoryId = cat.categoryId 
    GROUP BY cat.categoryId;

SELECT s.*, GROUP_CONCAT(ss.name) AS skills
    FROM Skills_Subcategory AS s
        JOIN Skills_rel_Subcategory_Skill AS srss
        ON srss.Subcategory = s.SubcategoryID
    JOIN Skills AS ss ON ss.skillID = srss.Skill
    GROUP BY s.SubcategoryID;

Results for the querys:

 ------------ ----------------- -------------------- 
| categoryId | name            | subcategory        |
 ------------ ----------------- -------------------- 
|          1 | Web development | Frontend,Backend   |
|          2 | Cybersecurity   | Blue team,Red team |
 ------------ ----------------- -------------------- 
2 rows in set (0.001 sec)

 --------------- ---------- ------------ -------------------------- 
| subcategoryId | name     | categoryId | skills                   |
 --------------- ---------- ------------ -------------------------- 
|             1 | Frontend |          1 | Sass,Css,Bootstrap,Figma |
|             2 | Backend  |          1 | Nodejs,Express,MySQL,PHP |
 --------------- ---------- ------------ -------------------------- 
2 rows in set (0.000 sec)

The desired result is the data being shown in json format for an api:

{"My skills":[
    {
        "categoryId":1,
        "name":"Web development",
        "subcategories":[
            "name":"Frontend",
            "skills":[
                "name":"Sass",
                "name":"Figma",
                "name":"Etc"
            ]
            "name":"Backend",
            "skills":[
                "name":"Etc"
            ]
        ]
    },
    {
        "categoryId":2,
        "name":"Cybersecurity",
        "subcategories":[
            "name":"Red team",
            "skills":[
                "name":"Etc"
            ]
            "name":"Blue tetam",
            "skills":[
                "name":"Etc"
            ]
        ]
    }
]}

CodePudding user response：

Haven't tested it. But since your queries are working and you need to do both with the same query, so here we go:

SELECT
    *
FROM
    (
        SELECT
            cat.*,
            GROUP_CONCAT(sc.name) AS subcategory
        FROM
            Skills_Category AS cat
            JOIN Skills_Subcategory AS sc ON sc.categoryId = cat.categoryId
        GROUP BY
            cat.categoryId
    ) AS t1
    LEFT JOIN (
        SELECT
            s.*,
            GROUP_CONCAT(ss.name) AS skills
        FROM
            Skills_Subcategory AS s
            JOIN Skills_rel_Subcategory_Skill AS srss ON srss.Subcategory = s.SubcategoryID
            JOIN Skills AS ss ON ss.skillID = srss.Skill
        GROUP BY
            s.SubcategoryID
    ) AS t2 ON t1.categoryId = t2.categoryId