JOIN vs UNION for Hiarchical Data

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 2000 Forums

Transact-SQL (2000)

JOIN vs UNION for Hiarchical Data

Author

Topic

kzielinski
Starting Member

2 Posts

Posted - 2006-03-12 : 19:48:26

I am trying to get a query to return a record set where there is a
parent - child relationship between some of the columns, such that
there will be several child records for a parent.

Runtime properties of the dataset:
Queires will retrive 0 - 5000 parent records (largest known case is about 2700 results)
There will 99% of the time there will be be 2 - 5 child records. however larger numbers are possilbe (largest know count being 150 child records).
The hiarchy is a flat 2 level hiarchy, there is no overlap between
parent and child records (no posiblity of recusion).

My question is which is more efficent
1) using JOIN and returning the parent data muyltiple times (once for each child).

2) Using a UNION of two quries one for the parent records and one for the child records. Somewhat along the lines of the universal table fromat employed in FOR XML EXPLICIT quries. This way
Parent records have the child columns set to NULL and child records
have the Parent Columns set to null (except for a key which identifies the parent)

3) Having the calling code execute a seperate querie for each parent to retrive the chidren. This is the current approach which i suspect is the least optimal (hence my desire to change it).

regards

Konrad

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-12 : 20:07:42

It would be easier to help if you posted the table structures, some sample data, and the output you are trying to get.

CODO ERGO SUM

kzielinski
Starting Member

2 Posts

Posted - 2006-03-12 : 21:36:21

TThe Whole point of the excersise is that the calling code will be converting the
query result into an XML document.

The folling is a simplification of the table structure.
In practive there are something like 10 items from the parent table and 4 items
from the child table. There are also a number of addional omited tables which need
to be joined to either the Parent table, or the Child Table, to get the actual desired value.

ParentTable.
IssueID, Subject, data2, data3 ... data10

ChildTable
IssueID ParticipantName.

what I want do do is get a result set which shows data from both tables. Note that
in practice I will have something like 8 - 12 items for parent table and 4 items from the child table.

1) do a join getting

issue ID, Subject ParticipantName
011 subject1 name1
011 subject1 name2
011 subject1 name3

012 subject2 name1

etc

2) Use a union, where the first query returns Parent data and the 2nd returns child
data. As Noted earlier this is same appeoach which FOR XML EXPLICIT queries table.

issueID Subject participantName
011 Subject1 NULL
011 NULL Name1
011 NULL Name2
011 NULL Name3

012 Subject2 NULL
012 NULL Name1

The calling code will use the above record set to produce the following output:
<issue-list>
<Issue id="011">
<subject>Subject1</subject>
<participants>
<participant>Name1</participant>
<participant>Name2</participant>
<participant>Name3</participant>
<participants>
</issue>
<issue id="012">
<subject>Subject2</subject>
<participants>
<participant>Name1</participant>
...
</participants>
</issue>
...
</issue-list>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources