SQL - how to expand this via sql only please?

[Nice4]

Need help from the SQL experts please...I have encountered a situation
that I need to expand a link-list like or tree like relationship into
a more details by using SQL only. This is the situation...a table with
2 fields that are interesting..
Here is the table with F1(parent) and F2 (child)
1 2 other fields....
1 A other fields....
1 3 other fields....
2 4 ...
2 B ..
A 5
A 6
3 C other fields....
.......more or called deeper down but unknow how deep it can go...you
can see the child can be as a parent of another child, like a tree
branchs out.

I wanted it to be expanded into something like this
1 2 other fields....
1 2 4 other fields....
1 2 4 B other fields....
1 A ....
1 A 5
1 A 6
1 3
1 3 C other fields....
......more ...

What gets me is that I must using SQL expression only in my Crystal
report and I am lost. I have tried some UNION/Sub-Query approach but
with no luck and cursor(s) may not be a solution that I wanted. Does
anyone can shed some lights and point me to the right direction
please.

[Pieter Linden]

Okay, so let me rephrase this to see if I understand what you're
saying...

You want to use SQL only to determine how deep the relationship
between tables goes? Hilarious. Can't be done. You might be able to
do it by walking the relationship hierarchy by using code, but not
with SQL.

[David Schofield]

On 14 Aug 2004 06:54:39 -0700, ni***@yahoo.com (Nice4) wrote:

Need help from the SQL experts please...I have encountered a situation
that I need to expand a link-list like or tree like relationship into
a more details by using SQL only. ......

Hi
(1) I am not putting myself forward as an SQL "expert"!

(2) AFAIK this can't be done in static (ie pure) SQL though since the
coming of xml some versions of SQL have extensions which can do this
sort of thing, eg in dB2. I don't know whether this includes later
versions of Access.

If you know the maximum depth of the hierarchy you can do it in pure
sql by having one query for each depth and forming a union of these
queries. This is only practical is the maximum depth is 10 I would
think, I have used it up to 6. You can include a query which tests if
there any deeper items so at least you get some output for the deeper
items.

Alternatively you could generate a temp structure using code and use
this as the input to your report. To be honest this would be easier to
generate as a string or file.

David Schofield

[Nice4]

Appreciated all replies.
- Agreed. with coding, there is way to expand them but it is not an
option in my situation here with Crystal Report. It seems it is a DB
design issue rather than 'impossible' SQL. I believe with my limited
experience, splits the relation(both fields in same table) into
separative tables may help. Any suggestions ?

- Let assumed, there is KNOWN number of level I wanted. As per your
suggestion, I can do something like this...Any sample please ?
select * from table
UNION
(select * from table
where F2 in (select F1 from table....)
UNION
( select * from table
where F2 in (select F1 from table....)
)
)

[Pieter Linden]

ni***@yahoo.com (Nice4) wrote in message news:<88**************************@posting.google. com>...

Appreciated all replies.
- Agreed. with coding, there is way to expand them but it is not an
option in my situation here with Crystal Report. It seems it is a DB
design issue rather than 'impossible' SQL. I believe with my limited
experience, splits the relation(both fields in same table) into
separative tables may help. Any suggestions ?

- Let assumed, there is KNOWN number of level I wanted. As per your
suggestion, I can do something like this...Any sample please ?
select * from table
UNION
(select * from table
where F2 in (select F1 from table....)
UNION
( select * from table
where F2 in (select F1 from table....)
)
)

If I understand right, I did something like this a while back. In my
case it was a series of college courses, each of which may or may not
have had prerequisites. So I had two tables:

CREATE TABLE Course(
CourseID LONG PRIMARY KEY,
CourseName TEXT(50),
....
)

CREATE TABLE Prereq(
CourseID LONG,
PrereqID LONG,
PRIMARY KEY (CourseID, PrereqID),
FOREIGN KEY CourseID REFERENCES Course.CourseID,
FOREIGN KEY PrereqID REFERENCES Course.CourseID);

What I wanted to know was the "depth" of the hierarchy.

I could do it with the QBE left joining Course to Prereq and then
Prereq to copies of itself until I ran the query and got only nulls in
one column. So how you would determine this without using code, I
have absolutely no idea.
In my example, I had something like
CourseID PrereqID
503 NULL
601 NULL
605 503
605 601
608 503
620 605
620 608

so *all* the prereqs for 620 are (608, 605, 601, 503)

If that's the kind of thing you're after, there's no way to determine
the "depth" of the join without some kind of code. Can't be done with
pure SQL.

I showed the hierarchy by outer joining Course to Prereq, and then
Prereq to itself multiple times until I came up with all nulls in my
rightmost column. I asked around here and found that there was no way
to do this without code of some type.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do a Google search on "Adjacency List Model" and "Nested Set Model."
You, probably, will get some articles by Joe Celko which describe "pure"
SQL methods to drill-down thru hierarchical tables. These methods are
also described in his book "Joe Celko's SQL for Smarties."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSElz4echKqOuFEgEQJYFgCg9wZbsYXRVaVgED6ueTd7lS CNKpUAmwQO
FGz6B2Z44K8VIUabNbbdqAtS
=HcON
-----END PGP SIGNATURE-----
Nice4 wrote:

Need help from the SQL experts please...I have encountered a situation
that I need to expand a link-list like or tree like relationship into
a more details by using SQL only. This is the situation...a table with
2 fields that are interesting..
Here is the table with F1(parent) and F2 (child)
1 2 other fields....
1 A other fields....
1 3 other fields....
2 4 ...
2 B ..
A 5
A 6
3 C other fields....
......more or called deeper down but unknow how deep it can go...you
can see the child can be as a parent of another child, like a tree
branchs out.

I wanted it to be expanded into something like this
1 2 other fields....
1 2 4 other fields....
1 2 4 B other fields....
1 A ....
1 A 5
1 A 6
1 3
1 3 C other fields....
.....more ...

What gets me is that I must using SQL expression only in my Crystal
report and I am lost. I have tried some UNION/Sub-Query approach but
with no luck and cursor(s) may not be a solution that I wanted. Does
anyone can shed some lights and point me to the right direction
please.

[--CELKO--]

>> What gets me is that I must using SQL expression only in my Crystal
report and I am lost. I have tried some UNION/Sub-Query approach but
with no luck and cursor(s) may not be a solution that I wanted. Does
anyone can shed some lights and point me to the right direction
please. <<

Here is the link on Amazon.com for my new book on "Trees & Hierarchies
in SQL"

http://www.amazon.com/exec/obidos/tg...roduct-details