Self Join in Mysql

Question

I have been trying to fetch records from same table depending on parent_id concept of child and parent. One extra column is sequence that defines sorting order.

My data is...

Sr.No | Name         | parent_id       | Sequence
1.    | Customer     | 0               | 0
2.    | orders       | 0               | 5
3.    | General      | 0               | 10
4.    | All          | 1               | 0
5.    | Purchased    | 1               | 5
6.    | Non-Purch    | 1               | 10
7.    | Pending      | 2               | 0
8.    | Change Pass  | 3               | 0
9.    | Logout       | 3               | 5

I want to fetch out data in single execution. Though I am not able to use outer join as it is not supported by mysql.

My Tried sql is..

SELECT b.* FROM soi_admin_menu a RIGHT JOIN soi_admin_menu b ON a.menu_id = b.parent_id WHERE 1=1 AND a.parent_id = 0 ORDER BY a.sequence, b.parent_id ASC

I want data like this in sequenced manner.

  --Customer
  --All
  --Purchased
  --Non-Purchased
  --Orders
  --Pending
  --General
  --Change Password
  --Logout

with all fields. My DB is MySQL

Answer 1

You cannot do it without OUTER JOINS, UNIONS or additional assumptions.

If you could assume, that Sequence and Sr.No give the same order for rows with parent_id = 0, you could try something like:

SELECT soi_admin_menu.*, 
    CASE 
        WHEN parent_id=0 THEN Sr.No * 10000 
        ELSE parent_id * 10000 + 1 + Sequence
    END as my_order 
FROM soi_admin_menu 
ORDER BY my_order;

Note that this solution gives you a limit on Sequence, id and parent (10000 in this case). To get rid of the limit, use string concatenation and sort by a string temporal column with a lower-than-any-character hash in case of the root rows.

With no OUTER JOINS, UNIONS or additional assumptions there is just no way to say which of

6.    | Non-Purch    | 1               | 10
7.    | Pending      | 2               | 0

goes first.

Answer 2

SELECT *,
   CASE
       WHEN parent_id=0 THEN Sr.No * 100
       ELSE parent_id * 100 + Sequence
   END as my_order
FROM soi_admin_menu a