i run the below script to get the left and right as per the binary i did and it is ok
Script
select a.ID,a.Node,a.direction,
a.Reference_ID,
a.Recommended_By
from
members a
JOIN
members b
ON
a.Node LIKE CONCAT('%(',b.ID,')%')
Where b.ID = 2
order by b.id,a.Recommended_By'
Result
ID Node Direction Reference Recommended_By
4 (2)(1) Left reem1 2
6 (2)(1) Right nevine1 2
9 (4)(2)(1) Left ghada1 4
11 (4)(2)(1) Right dina2 4
12 (6)(2)(1) Left amani2 6
13 (6)(2)(1) Right aulla2 6
18 (9)(4)(2)(1) Left amani3 9
19 (9)(4)(2)(1) Right dina3 9
I need to put CASE Statement to get left and right as a columns but i need it to be like :
ID Node Left Right Recommended_By
4 (2)(1) reem1 2
6 (2)(1) nevine1 2
9 (4)(2)(1) ghada1 4
11 (4)(2)(1) dina2 4
first two record to be sorted for left and right but the rest to be sorted as per recommended_By