sql query to get data for Spacetree (jit)

Question

I have a table "BinaryTree" and it has 2 columns "CustomerID" and "ParentID".

Another table is "Customers" and it has columns ""CustomerID" and "Firstname".

I want to query data from these table and want to assign these data to Spacetree in Json format.

Please have a reference of below links :-

http://philogb.github.io/jit/static/v20/Jit/Examples/Spacetree/example1.code.html

http://philogb.github.io/jit/static/v20/Jit/Examples/Spacetree/example1.html

I want data something like below :-

Parentid  CustomerID  FirstName
   1          34         Test1
   1          64         Test2
   1          46         Test3
   34         45         Test4
   34         102        Test5
   64         22         Test6
   46         54         Test7

So I can build json string and assign it to spacetree. I would be good if it return data in order, means for a parentid it first return all its childrens.

and after that it returns childrens of these childerns one by one, so it is easy to build json string in proper format that spacetree wants.

Please let me know if further information needed.

Thanks in advance guys.

Answer 1

use inner join.

select BT.PARID,BT.CUSTID,CU.firstname 
from BinaryTree BT INNER JOIN Customers CU on BT.CUSTID=CU.CUSTID
ORDER BY BT.PARID,BT.CUSTID

check the demo : http://sqlfiddle.com/#!3/1ffc1/1