Saturday, August 26, 2017

Trying to return all columns of child records for a parent record

By Hường Hana 3:30 PM Leave a Comment

I was searching for a solution by which I could get all the child records for a parent record. I found a solution that meet my needs as shown here

Only problem is that the above solution is concatenating the IDs.

Current Resultset

It is comma separated of ID column with values = 2,3,4

Expected Output

ID    Name    ParentID 1      1st       null 2      2nd       1 3      3rd       1 4      4th       2

I tried below code.

SELECT @pv:= (SELECT * FROM tblreport WHERE ParentID IN (@pv)) AS lv FROM tblreport JOIN (SELECT @pv:=2)tmp WHERE ParentID IN (@pv)

and got an error message : Operand should contain 1 column(s)

My sample SQL Fiddle

2 Answers

Answers 1

Now, they are coming comma separated. But, I am expecting to return all columns of a particular row.

You could use CSV ids result like:

SELECT * FROM tblreport WHERE FIND_IN_SET(ID,(SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (                      SELECT @pv:=(SELECT GROUP_CONCAT(ID SEPARATOR ',')                       FROM tblreport WHERE ParentID IN (@pv)) AS lv FROM tblreport                      JOIN (SELECT @pv:=1)tmp                        WHERE ParentID IN (@pv)) a));

DBFiddle Demo

Output:

ID  Name    ParentID 2   2nd     1 3   3rd     1 4   4th     2

EDIT:

If you need also original row you could use UNION ALL:

SET @var = 1;  SELECT * FROM tblreport WHERE FIND_IN_SET(ID,(SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (                      SELECT @pv:=(SELECT GROUP_CONCAT(ID SEPARATOR ',')                       FROM tblreport WHERE ParentID IN (@pv)) AS lv FROM tblreport                      JOIN (SELECT @pv:=@var)tmp                        WHERE ParentID IN (@pv)) a))  UNION ALL SELECT * FROM tblReport WHERE ID = @var ORDER BY ID;

DBFiddle Demo2

Answers 2

That output is simply provided by this short query:

SELECT * from tblreport WHERE FIND_IN_SET(id, '2,3,4');

Better yet...

Use MySQL 8.0 or MariaDB 10.2 -- each has recursive CTE queries which conveniently drill down a hierarchical database tree.

(Do not use CSV, only use separate rows.)

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment