I have a table like this-
id name ordering catid
1 parent1 1 0
2 parent2 2 0
3 parent3 3 0
4 child11 1 1
5 child12 2 1
6 child21 1 2
7 child22 2 2
8 child31 1 3
9 child32 2 3
I am trying to get the result like below-
id name ordering catid
1 parent1 1 0
4 child11 1 1
5 child12 2 1
2 parent2 2 0
6 child21 1 2
7 child22 2 2
3 parent3 3 0
8 child31 1 3
9 child32 2 3
I want to order the first parent(catid=0)
than its child than second parent and its child.
Is this possible to achieve desire result with these column.I tried join but not succeeded.
Surely I am not doing it correctly.
This is what I have tried-
SELECT a.*,c.name AS category_title FROM table AS a
LEFT JOIN table AS c ON c.id = a.catid
ORDER BY c.ordering asc, a.ordering asc
This query would do a SELF JOIN
on the same table, by joining the records from the first table with the corresponding parent records. The column parentId would contain the parent Id of the record if the parent existed, otherwise the ID of the record itself. The results are then ordered by parentId and then by the ID so that the parent always appears at the top in a given group of parent and its children.
SELECT
m.id,
m.name,
m.catid,
m.ordering,
p.ordering,
case
WHEN p.ordering IS NULL THEN m.ordering * 10
ELSE m.ordering + p.ordering * 10
END AS parentId
FROM
MyTable m
LEFT JOIN MyTable p
ON m.catid = p.id
ORDER BY parentId
The result is as follows:
1 parent1 0 1 10
4 child11 1 1 1 11
5 child12 1 2 1 12
2 parent2 0 2 20
6 child21 2 1 2 21
7 child22 2 2 2 22
3 parent3 0 3 30
8 child31 3 1 3 31
9 child32 3 2 3 32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With