mysql self-join fixing result order when parent is doesnot exists

Question

I i have a table named Taxonomy it preaty much holds everything that has to do with the structure of the organization. as a school example table looks like

id | name | type | parent
1  | 2014 | year | null
2  | igcse| dep  | 1
3  | kg1  | grade| 2
4  | c1   | class| 3

4 types, upper most is the school year (2014-2015) for example. under it school department, under it grade (grade1,2,3 etc) under it multiple classes.

when i need to get this i run a self join query like this :

SELECT y.name AS year,
       d.name AS dep,
       g.name AS grade,
       c.name AS class,
       e.name AS exam
FROM `taxonomys` e
left JOIN `taxonomys` c on c.id = e.parent
left JOIN `taxonomys` g on g.id = c.parent
left JOIN `taxonomys` d on d.id = g.parent
left JOIN `taxonomys` y on y.id = d.parent
where c.type in ('grade','department','class','year')

its working fine except with the so many nulls i get ! example result of query

as you can see, the classes shows correctly with year under year field,

yet on first row, year is under Class field, (shifted 3 cells).

when ever there is a null value is shifted. how can i fix that ?

thanks alot

EDIT

What is this table ?

A variation of Adjacency List Model, I added an extra column named type so that I can identify level of any row without having to retrieve whole path.

Table hold the structure of the school. example

In every new year, they create A Year example "2014-2015", then inside this year creates the school different departments "american diploma, Highschool, playshool, etc.." under that comes the school grades and under every grade come the classes..

Example

id name        type   parent
1  2014-2015   year   null       
2  highschool  dep.   1
3  grade 10    grade  2
4  grade 11    grade  2
5  class a     class  3 
6  class b     class  3

These inputs means

   2014-2015
   |__Highschool
             |__ grade 10
                   |__ class a  
                   |__ class b
             |__ grade 11

after than another table link students to Class node, and another table link posts to class's and so on.

so this table basically holds the school structures. since years, departments, and grades are only there for organization of the school users "there is no data except names needed for them" i decided to have the all in one table.

Why did you build it in this a very very very bad/anti-pattern design ?

its actually working very nice for me ! (we hv 4 years with all the departments and students and classes, over 100k posts linked to user/class and over 10k users and its working smooth so far !)

Answer 1

I don't know what your output is supposed to be, and I don't know how your query is suppposed to get it, and I don't know how you are encoding what information in your table, but I suspect that the query below gives the result you want, and that it's somewhat redundant (namely the last 4 lines), and that your design is really, really, really an anti-pattern.

/*
rows y.n,d.n,g.n,c.n,e.n
where
    taxonomies(y.i,y.n,'year',y.p)
AND taxonomies(d.i,d.n,'dep',y.i) AND taxonomies(g.i,g.n,'grade',d.p)
AND taxonomies(c.i,c.n,'class',g.i) AND taxonomies(e.i,e.n,'exam',c.i)
*/
SELECT y.name AS year,
       d.name AS dep,
       g.name AS grade,
       c.name AS class,
       e.name AS exam
FROM `taxonomys` y
JOIN `taxonomys` d on y.id = d.parent
JOIN `taxonomys` g on d.id = g.parent
JOIN `taxonomys` c on g.id = c.parent
JOIN `taxonomys` e on c.id = e.parent
WHERE y.type = 'year'
AND d.type = 'dep'
AND g.type = 'grade'
AND c.type = 'class'
AND e.type = 'exam'

Answer 2

They are shifting to the left and you are doing a left JOIN. That's a clue. I suggest to change it to only join. When you perform a left JOIN you get all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

The columns shifting places is new to my eyes (not a SQL expert here though) but it seems to me that it could be related with the fact that you are retrieving all the data from the same table taxonomys.