CODEWITHSUNDEEP
mysqlsql
zkanoca
zkanoca

Reputation: 9918

Mysql Adjacency List Model, My query result is not what I want

Yesterday, I have asked this question. I decided to change my approach and tried something new. Before starting please look at my table structure below.

I have a table named as People:

id | name |parent_id
---+------+---------
1  | John | 0
2  | Jane | 1
3  | James| 1
4  | Jack | 0
5  | Jim  | 4
6  | Jenny| 4
7  | Mike | 0

So john is parent of Jane and James. Tree goes like this.

John
-Jane
-James
Jack
-Jim
-Jenny
Mike

The difference of the question starts with Mike. Mike has no parent or child. Just somebody lonely. So When I query my table with the following query, I can't see Mike in the resultset

SELECT 
    t1.name as level1, t2.name as level2 
FROM 
    People as t1 
JOIN 
    People as t2 ON t2.parent_id = t1.id
ORDER BY 
    level1, level2

This query brings the rows if it has child item(s) to the field level1.

Example:

level1 | level2
-------+-------
John   | Jane
John   | James
Jack   | Jim
Jack   | Jenny

How can I show Mike in the result? WHat should I change in my query?

Upvotes: 1

Views: 226

Answers (1)

valex
valex

Reputation: 24144

Mike has no children so just add such persons with UNION to your query. Also you should use INNER JOIN instead of LEFT JOIN

select * from 
(
    SELECT 
        t1.name as level1, t2.name as level2 
    FROM 
        People as t1 
    JOIN 
        People as t2 ON t2.parent_id = t1.id
    UNION
    select t1.Name as level1, '' as level2 
        from People t1 
        where Parent_id=0 
        and not exists(select 1 from People where Parent_id=t1.Id)

) People 

ORDER BY 
    level1, level2

SQLFiddle demo

Upvotes: 2

Related Questions