Ordering and grouping togheter on multi tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Ordering and grouping togheter on multi tables

Author

Topic

Scal
Starting Member

24 Posts

Posted - 2011-06-17 : 08:34:00

Hi;
I'm struggling a little to join tables AND group / order records.
Let me explain the structure:
- table A
--- ID
- table A2B
--- tableA_ID
--- tableB_ID
--- ordernumber
- table B
--- ID
- table B2C
--- tableB_ID
--- tableC_ID
--- ordernumber
- table C
--- ID

Each relational tables (*2*) have a composite FK on both *_ID fields.
I can have the same values for different records, many to many relation.

What I do now is join all tables but I get an issue into making sure the ordering is correct.
I need table A2B, B2C to be ordered but keep the group of joins.

Data example:

table A
ID | Name
---------------------------------------------
1 | tbl-a name 1
2 | tbl-a name 2

table B
ID | Name
---------------------------------------------
1 | tbl-b name 1
2 | tbl-b name 2

table C
ID | Name
---------------------------------------------
1 | tbl-c name 1
2 | tbl-c name 2

table A2B
tableA_ID | tableB_ID | ordernumber
---------------------------------------------
1 | 1 | 1
1 | 1 | 2
1 | 2 | 3
1 | 1 | 4

table B2C
tableB_ID | tableC_ID | ordernumber
---------------------------------------------
2 | 2 | 1
1 | 1 | 2
2 | 1 | 3
2 | 2 | 4

How can I get all entries in an ordered and grouped way?
When I order on all order numbers, my result-set can mix groups of tables.

Using loops, it would look like:
- start loop [table A join table A2B join table B] order by ordernumber
--- start loop [table B join table B2A join table C] order by ordernumber

How could I do this? Am I forced to use cursor and temporary table or sub-queries or something else?

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-17 : 09:03:28

Probably a subquery or cte.
What do you have at the moment?

It sounds just like a union - ordered by the result from each union query - just add an id to the query hard coded for the union statement.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Scal
Starting Member

24 Posts

Posted - 2011-06-21 : 05:34:20

The SP query I have right now is:

SELECT
P.BaseUrl AS ProjectBaseUrl,
TS.ID AS TestSuiteID,
TS.Name AS TestSuiteName,
TS.IsActive AS TestSuiteIsActive,
TC.ID AS TestCaseID,
TC.Name AS TestCaseName,
TC.IsActive AS TestCaseIsActive,
TC.IsDeleted AS TestCaseIsDeleted,
TA.ID AS TestActionID,
TA.Command AS TestActionCommand,
TestActionArg1 =
CASE
WHEN TA2B.Argument1 IS NOT NULL THEN TA2B.Argument1
ELSE ISNULL(TA.Argument1,'')
END,
TestActionArg2 =
CASE
WHEN TA2B.Argument2 IS NOT NULL THEN TA2B.Argument2
ELSE ISNULL(TA.Argument2, '')
END,
TA.IsActive AS TestActionIsActive,
TA.IsDeleted AS TestActionIsDeleted
FROM
Projects AS P
INNER JOIN ProjectsToTestSuites AS P2TS ON P.ID = P2TS.Projects_ID
INNER JOIN TestSuites AS TS ON P2TS.TestSuites_ID = TS.ID
INNER JOIN TestSuitesToTestCases AS TS2TC ON P2TS.TestSuites_ID = TS2TC.TestSuites_ID
INNER JOIN TestCases AS TC ON TS2TC.TestCases_ID = TC.ID
INNER JOIN TestCasesToTestActions AS TC2TA ON TC.ID = TC2TA.TestCases_ID
INNER JOIN TestActions AS TA ON TC2TA.TestActions_ID = TA.ID
LEFT JOIN TestActionsToBrowsers AS TA2B ON TA.ID = TA2B.TestActions_ID AND TA2B.Browsers_ID = @BrowserID
WHERE
P.ID = @ProjectID
AND
TS.IsDeleted = 0
AND
TC.IsDeleted = 0
AND
TA.IsDeleted = 0
ORDER BY
P2TS.OrderNumber,
TS2TC.OrderNumber

If I add "TC2TA.OrderNumber" in the ordering list, result is not grouped by projects, testsuites and testcases anymore.

Here's the diagram: http://pascalc.nougen.com/stuffs/diagram.png

I need the result-set to be build as if I was doing:
- Loop select from testsuites where projectid = X order by OrderNumber
- - Loop select from testcases where projectid = X order by OrderNumber
- - - Loop select from testactions where projectid = X order by OrderNumber
- - - End loop
- - End loop
- End loop

Thanks for the help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-21 : 07:10:13

Note - what you have will get duplicates - if there are 2 test cases and 2 test suites for a project you will get 4 rows.

Something like

select type = 1, ...
from Projects AS P
INNER JOIN ProjectsToTestSuites AS P2TS ON P.ID = P2TS.Projects_ID
INNER JOIN TestSuites AS TS ON P2TS.TestSuites_ID = TS.ID
union all
select type = 3, ...
from Projects AS P
INNER JOIN TestSuitesToTestCases AS TS2TC ON P2TS.TestSuites_ID = TS2TC.TestSuites_ID
INNER JOIN TestCases AS TC ON TS2TC.TestCases_ID = TC.ID
union all
select type = 3, ...
from Projects AS P
INNER JOIN TestCasesToTestActions AS TC2TA ON TC.ID = TC2TA.TestCases_ID
INNER JOIN TestActions AS TA ON TC2TA.TestActions_ID = TA.ID
order by ProjectID, OrderNumber, type

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Scal
Starting Member

24 Posts

Posted - 2011-06-23 : 10:26:01

Hi nigelrivett;
I've been trying to use the UNION ALL as suggested, but I'm struggling. From my understanding of the MSDN doc, both SELECT combined with UNION needs to have the same number of columns and names (only column names from 1st select will be returned).

I can manage to build the same column list for all queries, assigning a default value if it does not exists but where I'm struggling is to make each select return the correct information to UNION them all.

Any trick/tip for doing this?
Should I build all queries then "merge them" with UNION as last step?
Thanks

Scal
Starting Member

24 Posts

Posted - 2011-06-23 : 11:20:09

I just realise the DB structure has changed a little.
I will make a new topic or this will get too confusing for all, including me :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources