Help with the Following SQl Logic

[Vimalsoft Pty Ltd]

Good Day all it is Probably a long day, i cant think Straight now. i have a table that looks like this

Nodeid Parent Description Type Curr

89 NULL Compulsory 1 10
90 89 B1052 3 10
2820 89 One of 2 10
4113 89 B1061 3 10
2821 2820 B1054 3 10
2822 2820 B1055 3 10

Now the Bold Record needs to be on top of "One of" because the Parent is "Compulsary" with the "Parent" = 89. Now Even "One of " has the Same Parent as the Bold record but if its a "One of " and they have the same parent, then "One of " must always be below the record. What i mean is that if there is a record with a same parent but different Type , the one that has type 2 should go below the one that has type 3 in my query. here is my query

select distinct nP.id, nP.NodeID, nP.parent, nP.Description, nRef.ID refParent, np.type
from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent
order by refParent,nP.id,np.type desc

Thank you

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Vimalsoft Pty Ltd]

Thanks now i have Changed my Code to look like this and its working perfect in SQl when i test it.

select distinct nP.id, nP.NodeID, nP.parent, nP.Description, nRef.ID refParent, np.type
from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent
order by np.Parent,np.Description,np.NodeID

And it returns

Nodeid Parent Description Type Curr

89 NULL Compulsory 1 10
90 89 B1052 3 10
4113 89 B1061 3 10
2820 89 One of 2 10
2821 2820 B1054 3 10
2822 2820 B1055 3 10

and its Good thanks. am binding the values to a Tree Control like this

public void PopulateTreeFromCurr(int currID)
{
// CurrStructDataSource calls sp_Traverse_Tree which returns an inordered list of nodes for a tree
IEnumerable result = CurrStructDataSource.Select(DataSourceSelectArguments.Empty);
// ID, Parent, Child, Description, Level, i
int Parent, Child;
CurriculumTreeView.Nodes.Clear();

    ArrayList CurrNodes = new ArrayList();

    if (result != null)
    {
        // an ordered set of nodes is given. parent always before current node except for root            
        foreach (System.Data.DataRowView row in result)
        {
            TreeNode newnode = new TreeNode(row\["Description"\].ToString(), row\["NodeID"\].ToString());
            CurrNodes.Add(newnode); // should setup a lookup between the id given by the ArrayList and that of the node
            // as we add the nodes, we can set up the hierarchy
            if (row\["refParent"\].ToString() == "")
            {   // don't have to add the root node to another node-it is the root
            }
            else
            {
                Parent = Convert.ToInt32(row\["Parent"\]);
                Child = Convert.ToInt32(row\["ID"\]);

                TreeNode ParentNode = new TreeNode();
                TreeNode ChildNode = new TreeNode();
                \[COLOR="DarkOrange"\]\[B\]ParentNode = (TreeNode)CurrNodes\[Parent\];\[/B\]\[/COLOR\]
                ChildNode = (TreeNode)CurrNodes\[Child\];
                ParentNode.ChildNodes.Add(ChildNode);
                CurrNodes\[Parent\] = ParentNode;