Question 20001 in Mysql Question posted in
The official MySQL documentation can be found here.

Get Parent_ID by list of Child_Id in SQL – Mysql

I have a mapping table of Parent_Node and Child_Node- Parent_Child

A PARENT can have multiple CHILDs, and a CHILD can be a part of multiple PARENTs.

create table Parent_Child(Parent_Id int,Child_Id int);
INSERT INTO Parent_Child VALUES( 219,217);
INSERT INTO Parent_Child VALUES( 219,218);
INSERT INTO Parent_Child VALUES( 221,172);
INSERT INTO Parent_Child VALUES( 221,217);
INSERT INTO Parent_Child VALUES( 221,218);
INSERT INTO Parent_Child VALUES( 222,47);
INSERT INTO Parent_Child VALUES( 222,172);
INSERT INTO Parent_Child VALUES( 222,217);
INSERT INTO Parent_Child VALUES( 222,218);
INSERT INTO Parent_Child VALUES( 223,47);
INSERT INTO Parent_Child VALUES( 223,160);
INSERT INTO Parent_Child VALUES( 223,172);
INSERT INTO Parent_Child VALUES( 223,217);
INSERT INTO Parent_Child VALUES( 223,218);
INSERT INTO Parent_Child VALUES( 226,47);
INSERT INTO Parent_Child VALUES( 226,160);
INSERT INTO Parent_Child VALUES( 226,172);
INSERT INTO Parent_Child VALUES( 226,217);
INSERT INTO Parent_Child VALUES( 226,218);
INSERT INTO Parent_Child VALUES( 226,224);
INSERT INTO Parent_Child VALUES( 226,225);
INSERT INTO Parent_Child VALUES( 227,218);
INSERT INTO Parent_Child VALUES( 227,224);
INSERT INTO Parent_Child VALUES( 227,225);
INSERT INTO Parent_Child VALUES( 228,218);
INSERT INTO Parent_Child VALUES( 228,225);

I need to find a PARENT, having only the list of specific CHILDs.
Such as,

INPUT (CHILD_ID) = 217, 218 ||
EXPECTED OUTPUT = PARENT_ID- 219

Could anyone help in getting the correct SQL? Tried with below SQL, but didn’t worked–


    SELECT * from Parent_child_rltnp
        group by PARENT_ID
        having count(DISTINCT CHILD_ID) = 2 AND CHILD_ID IN (217, 218);

it fetched me two records of PARENT_IDs- 219, 228
instead of returning only one PARENT_ID- 219(having exact match of CHILD_Ids)

We are using MYSQL v5.7

Tags:

2

Answers


  1. 0 
    create table Parent_Child(Parent_Id int,Child_Id int);
INSERT INTO Parent_Child VALUES( 219,217);
INSERT INTO Parent_Child VALUES( 219,218);
INSERT INTO Parent_Child VALUES( 221,172);
INSERT INTO Parent_Child VALUES( 221,217);
INSERT INTO Parent_Child VALUES( 221,218);
INSERT INTO Parent_Child VALUES( 222,47);
INSERT INTO Parent_Child VALUES( 222,172);
INSERT INTO Parent_Child VALUES( 222,217);
INSERT INTO Parent_Child VALUES( 222,218);
INSERT INTO Parent_Child VALUES( 223,47);
INSERT INTO Parent_Child VALUES( 223,160);
INSERT INTO Parent_Child VALUES( 223,172);
INSERT INTO Parent_Child VALUES( 223,217);
INSERT INTO Parent_Child VALUES( 223,218);
INSERT INTO Parent_Child VALUES( 226,47);
INSERT INTO Parent_Child VALUES( 226,160);
INSERT INTO Parent_Child VALUES( 226,172);
INSERT INTO Parent_Child VALUES( 226,217);
INSERT INTO Parent_Child VALUES( 226,218);
INSERT INTO Parent_Child VALUES( 226,224);
INSERT INTO Parent_Child VALUES( 226,225);
INSERT INTO Parent_Child VALUES( 227,218);
INSERT INTO Parent_Child VALUES( 227,224);
INSERT INTO Parent_Child VALUES( 227,225);
INSERT INTO Parent_Child VALUES( 228,218);
INSERT INTO Parent_Child VALUES( 228,225);

    Query:

    SELECT Parent_Id ,count(*) child_count,count(case when child_id in (217,218) then 1 end )input_child_count  from Parent_Child
group by Parent_id
having child_count=input_child_Count

    Output:

    Parent_Id child_count input_child_count
    219 2 2

    fiddle

    Login or Signup to reply.
  2. 0

    Below you will find 3 different alternatives giving the same expected result

    Alternative I.

    SELECT Parent_Id
FROM Parent_Child 
GROUP BY Parent_Id
HAVING MIN(Child_Id) = 217
   AND MAX(Child_Id) = 218;

    Alternative II.

    SELECT Parent_Id
FROM Parent_Child
GROUP BY Parent_Id
HAVING SUM(Child_Id in (217,218)) = COUNT(*);

    Alternative III

    SELECT Parent_Id
FROM Parent_Child 
GROUP BY  Parent_Id
HAVING GROUP_CONCAT(DISTINCT Child_Id order by Child_Id asc ) ='217,218';

    Demo

    Reference

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search