GROUP BY parent_id column with id column

Question

I have my domain table.

+-----------+-----------+
| domain_id | parent_id |
+-----------+-----------+
|        12 |      NULL |
|        14 |      NULL |
|        13 |        12 |
|        15 |        14 |
|        16 |        14 |
|        19 |        14 |
|        18 |        15 |
|        17 |        16 |
+-----------+-----------+

parent_id is many to one relationship with domain_id. However when I query, I want to group all of the parent_id's under the right domain id, but I'm having issues with that.

MySQL doesn't support recursive queries.. you need to do this in another programming language... the only way around it is if you know the number of levels deep the child goes — John Ruddell, Sep 27 '14 at 16:27
possible duplicate of [MySQL hierarchical recursive query](http://stackoverflow.com/questions/20215744/mysql-hierarchical-recursive-query) — John Ruddell, Sep 27 '14 at 16:29
also please read [**My Post**](http://meta.stackoverflow.com/a/271056/2733506) on asking a proper question.. you have missing parts in your question — John Ruddell, Sep 27 '14 at 16:30

John Ruddell · Accepted Answer · 2014-09-27T17:21:15.193

Assuming you have the domain name associated as well and just didn't provide it you can do it like this.. but you would have to know how many levels deep to go.. and those levels would be created by each left join

Setup:

CREATE TABLE domains (
    id int, 
    parent int, 
    name varchar(255)
); 
INSERT INTO domains VALUES
(12, NULL, "Dell.com"),
(14, NULL, "Sprint.com"),
(13, 12, "Dell.net"),
(15, 14, "Sprint.net"),
(16, 14, "Sprint.edu"),
(19, 14, "TechSprint.com"),
(18, 15, "SupportSprint.net"),
(17, 16, "SupportSprint.edu");

Query:

SELECT 
    d1.name as level1, 
    d2.name as level2, 
    d3.name as level3, 
    d4.name as level4
FROM domains AS d1
LEFT JOIN domains AS d2 ON d2.parent = d1.id
LEFT JOIN domains AS d3 ON d3.parent = d2.id
LEFT JOIN domains AS d4 ON d4.parent = d3.id
WHERE d1.name = "Sprint.com"

Result:

+------------+-------------------+-------------------+--------+
|    NAME    |       LEV2        |       LEV3        |  LEV4  |
+------------+-------------------+-------------------+--------+
| Sprint.com | Sprint.net        | SupportSprint.net | (null) |
| Sprint.com | Sprint.edu        | SupportSprint.edu | (null) |
| Sprint.com | TechSprint.com    | (null)            | (null) |
+------------+-------------------+-------------------+--------+

Fiddle Demo

Note:

the WHERE in this query is a filter to go down the path for one domain.. if you remove the WHERE then you will get the path for all.. but there will be a lot of null values also you need to know how many children there are for a domain... because you wont know how many LEFT JOINS to do to the table for the children