TOL TOL - 3 years ago 75
MySQL Question

MYSQL: select parent sorted alphabetically grouped with children sorted alphabetically

I've made a simple table to understand parent-child relationship, but I don't seem to get proper results. I want to sort it


  1. first alphabetically by 'name' field, only those who have no parent (parent_id==0)

  2. BUT, whenever there are child rows for the parent, they should be printed after the parent, also sorted alphabetically by name.



Example table:

============================================================

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `parent_id`, `name`) VALUES
(1, 4, 'Zorg'),
(2, 0, 'Woordolack'),
(3, 4, 'Akriller'),
(4, 0, 'Metabrusher'),
(5, 2, 'Intersplitter'),
(6, 0, 'Beaverbrain'),
(7, 4, 'Torgeoruos'),
(8, 2, 'Deptezaurus');


============================================================

Here is what I want to output to a web browser using PHP:



Beaverbrain
Metabrusher
-> Akriller
-> Torgeoruos
-> Zorg
Woordolack
-> Deptezaurus
-> Intersplitter


============================================================

I should say that this is a sample table just to show the idea, so it can be modified in any way to get the proper result.

Answer Source

You can try this query

SELECT IF(child = '',parent,CONCAT('->',child)) as value 
FROM
    (SELECT parent.name as parent,child.name as child
     FROM test parent 
       INNER JOIN 
       (SELECT id,parent_id,name FROM test 
        UNION 
        SELECT null,id,'' FROM test
        WHERE parent_id = 0)child
     WHERE parent.parent_id = 0
     AND child.parent_id = parent.id
     ORDER BY parent,child
     )parent_child

sqlFiddle

What the query does is INNER JOIN test with test and find out which are parent and child. The inner UNION SELECT null,id,'' FROM test WHERE parent_id = 0 creates an empty child(ren) for display purposes. Then the outter SELECT sees if the child is '' it prints out parent, otherwise it concats -> in front of child. and so you get your result like how you wanted.

The below version might be a little faster. Since we'll just create parents with NULL child instead of using children as part of INNER JOIN (above).

SELECT IF(child IS NULL,parent,CONCAT('->',child)) as value 
FROM
 (SELECT parent.name as parent,child.name as child
    FROM test parent 
   INNER JOIN test child ON child.parent_id = parent.id
   WHERE parent.parent_id = 0
  UNION
  SELECT test.name as parent,NULL as child    #This creates a NULL child row
    FROM test WHERE test.parent_id = 0        #for parent display purpose
  ORDER BY parent,child
 )parent_child

sqlFiddle

I would however use the below query instead and inside PHP, check to see if the isParent flag is 1 or 0 and then show the appropriate fields.

SELECT 0 as isParent,parent.name as parent,child.name as child,
       parent.id as parentId,child.id as childId
  FROM test parent 
 INNER JOIN test child ON child.parent_id = parent.id
 WHERE parent.parent_id = 0
UNION
SELECT 1 as isParent,test.name as parent,NULL as child,
       test.id as parentId,NULL as childId
  FROM test WHERE test.parent_id = 0       
ORDER BY parent,child

sqlFiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download