How to get parent with at least 1 child?

Question

I have categories table like below:

category_id  name            parent_id
1            category1       NULL
2            subcategory1    1
3            category2       NULL
4            subcategory2    1
5            subcategory4    NULL
6            subcategory3    1
7            subcategory8    NULL
8            subcategory9    5
9            subcategory5    6
10           subcategory10   6
11           subcategory13   6
12           subcategory6    4
13           subcategory7    4
14           subcategory12   4
15           subcategory17   4
16           subcategory10   4
17           subcategory19   4

I want to fetch data of all those parents who are having at least one child and fetch all children's data first 2 rows per parent.

Below is the effort I tried until now.

SELECT c.category_id, c1.name, 
FROM categories c
LEFT JOIN categories c1 ON c1.category_id = c.category_id where count(c1.id) > 1 
limit 2;

My expected output:

category_id parent_id   name            parent_name 
2           1           subcategory1    category1   
4           1           subcategory2    category1
8           5           subcategory9    subcategory4
9           6           subcategory5    subcategory3
10          6           subcategory10   subcategory3
12          4           subcategory6    subcategory2
13          4           subcategory7    subcategory2

Every category has its own record in table along with parent id, so for every category id with parent id NOT NULL we can fetch name for that parent id from same table. — Rahul, Jun 07 '19 at 10:15
Possible duplicate [get top n groups](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Sam, Jun 07 '19 at 11:42
@ElSam Can you please map that answer for my requirement because I have lack knowledge to do this? — Rahul, Jun 07 '19 at 11:48

score 1 · Accepted Answer · answered Jun 07 '19 at 13:53

   WITH MyRowSet
    AS
    (
    SELECT NAME
          ,PARENT_ID
            ,ROW_NUMBER() OVER (PARTITION BY PARENT_ID ORDER BY PARENT_ID) AS RowNum
    FROM categories 
    )
    SELECT NAME,PARENT_ID FROM MyRowSet WHERE PARENT_ID IS NOT NULL AND RowNum <= 2

score 0 · Answer 2 · answered Jun 07 '19 at 11:01

Here you are

SELECT
    child AS category_id,
    parent AS parent_id,
    (SELECT name FROM sql01 WHERE category_id = child) as name,
    (SELECT name FROM sql01 WHERE category_id = parent) as parent_name
FROM
(
    SELECT
        min(category_id) AS child,
        parent_id AS parent
    FROM
        sql01
    WHERE
        parent_id IS NOT NULL
    GROUP BY
        parent_id
    UNION
    SELECT
        min(category_id) AS child,
        parent_id AS parent
    FROM
        sql01
    WHERE
        parent_id IS NOT NULL
        AND category_id NOT IN
        (
            SELECT
                min(category_id) AS child
            FROM
                sql01
            WHERE
                parent_id IS NOT NULL
            GROUP BY
                parent_id
        )
    GROUP BY
        parent_id
    ORDER BY
        child ASC,
        parent ASC
) as ids
;

Mate, please explain otherwise I will be **unconscious**. This is too much for me :| — Rahul, Jun 07 '19 at 11:02
sql01 is the source table (the same structure as in your question) and there is a rule that a child can only have one parent. The result is what you are looking for. — Was, Jun 07 '19 at 13:21

score 0 · Answer 3 · answered Jun 07 '19 at 11:13

I think it is use full for you.

select  c.category_id,c.parent_id,c.name     
,(select name from  categories where category_id=c.parent_id limit 1 ) as parentName
from categories c where c.parent_id in (
select distinct parent_id  from categories where parent_id IS NOT NULL);

score 0 · Answer 4 · answered Jun 07 '19 at 12:25

Run the below query, hope you get exact what you want and let me know any issue for the same.

// type this if ONLY_FULL_GROUP_BY is not set. if you are using latest version of mysql than no need to add this line.
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

set @num := 0, @type := '';
SELECT c2.id, c2.parent_id, c2.name, c1.name as parent_name,
      @num := if(@type = c2.parent_id, @num + 1, 1) as row_number,
      @type := c2.parent_id as dummy
FROM mst_category c1
left Join mst_category c2 ON c1.id = c2.parent_id
where c2.parent_id IS NOT NULL 

having row_number <= 2;

score 0 · Answer 5 · answered Jun 07 '19 at 12:47

This is an adaption to your problem inspired by responses posted in the get top n group of grouped results.

Since you didn't provide your Mysql server's version, it wasn't clear if some new functions would work for you, so this response tries to simulate ROW_NUMBER() function:

SELECT
    category_id, name, parent_id 
FROM
(
    SELECT
        name, parent_id, category_id, 
        @rn := IF(@prev = parent_id, @rn + 1, 1) AS rn,
        @prev := parent_id
    FROM categories
    JOIN (SELECT @prev := NULL, @rn := 0) AS vars
          where parent_id is not null
          ORDER BY category_id, parent_id
) AS T1
WHERE rn <= 2;

Here's the original comment.

How to get parent with at least 1 child?

5 Answers5