2

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

Bartosz Marcinkowski
  • 6,651
  • 4
  • 39
  • 69
Sankalp
  • 1,300
  • 5
  • 28
  • 52
  • 1
    outer join not supported? [I don't think that's correct](http://dev.mysql.com/doc/refman/5.0/en/outer-join-simplification.html), also see [this question](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql) for more info on how to emulate full outer joins. – NDM Sep 16 '13 at 08:29
  • Exactly thats true. The user to question that you have mentioned have solved using Keyword 'UNION'. – Sankalp Sep 16 '13 at 08:32
  • Can you explain what it is that you want? Your list of rows seems quite arbitrary to me; in terms of sequence-ID and parent-ID, you've posted (0,0), (1,0), (1,5), (1,10), (0,5), (2,0), (0,10), (3,0), (3,5). I don't see logic. – ruakh Sep 16 '13 at 08:37
  • Simply I ahve fruits (Apple, Mango, Pineappli), Vegetables (Potato, Onion), Fatfood(Pizza, Burger)etc I have this form of data I want to divide data in same format as written above according to sequence it have. Means First record to be Fruits then Apple, Mango, Pineapple, then 5 record as Vegetables 6 record is POtato thn, Oninon.. I hope you are now clear. – Sankalp Sep 16 '13 at 08:41

2 Answers2

3

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.

Bartosz Marcinkowski
  • 6,651
  • 4
  • 39
  • 69
1
SELECT *,
   CASE
       WHEN parent_id=0 THEN Sr.No * 100
       ELSE parent_id * 100 + Sequence
   END as my_order
FROM soi_admin_menu a
david
  • 3,225
  • 9
  • 30
  • 43