SQL Server 2008: many to many tables with relational tables ordering field + grouping

To understand what I need, here is the table's I'm u开发者_如何学Csing diagram: http://pascalc.nougen.com/stuffs/diagram.png

I need to get a project's properties + all it's relation, all listed based on the corresponding relational tables' OrderNumber column.

Let's say I need "Project Z", I want to get:

• Project's BaseUrl, ... where ID = @ID
• All testsuites associated to that project, listed by ProjectsToTestSuites.OrderNumber
• All testcases associated to the matching testsuites, listed by TestSuitesToTestCases.OrderNumber
• All testaction associated to the matching testcases, listed by TestCasesToTestActions.OrderNumber

So far, all my attempts are returning back results with mixed ordering. A testcase is mixed inside a testsuite it doesn't belong to and alike.

I try to avoid using cursors (loop each relation in specific order required), tried the use of UNION but couldn't get it to work either.

I wouldn't have troubles with cursor but if a solution exists withuout the need to use it, I prefer of course.

Thanks

If you want a flat result you could start with something like this (untested)

select
    p.Id,
    p.BaseUrl,
    ts.*,
    tc.*,
    ta.*,
    pts.OrderNumber as SuitesOrder,
    tstc.OrderNumber as CasesOrder,
    tcta.OrderNumber as ActionsOrder
from
    Projects p
join
    ProjectToTestSuites pts
    on pts.Projects_Id = p.Id
join
    TestSuites ts
    on ts.Id = pts.TestSuites_Id
join
    TestSuitesToTestCases tstc
    on tstc.TestSuites_Id = ts.Id
join
    TestCases tc
    on tc.Id = tstc.TestCases_Id
join
    TestCasesToTestActions tcta
    on tcta.TestCases_Id = tc.Id
join
    TestActions ta
    on ta.Id = tcta.TestActions_Id
where
    p.Id = @Id
order by
    pts.OrderNumber,
    tstc.OrderNumber,
    tcta.OrderNumber