can MySQL do something like Oracle START WITH and CONNECT BY?

[Flip]

I know this might not be the best place for this, but I don't have access to
the MySQL newsgroups here. :<

At work we're using Oracle and using START WITH and CONNECT BY to get a
hierarchical tree in our data. I would like to know if MySQL can do the
same thing? I tried googling this but wasn't able to find anything. :< I
would like to know if MySQL can do this, cause if it can, I would like to
use this functionality in a webapp.

Thanks.

 

[Tim Ward]

At work we're using Oracle and using START WITH and CONNECT BY to get a
hierarchical tree in our data. I would like to know if MySQL can do the
same thing? I tried googling this but wasn't able to find anything. :< I
would like to know if MySQL can do this, cause if it can, I would like to
use this functionality in a webapp.

Well, you can certainly model tree structured data in MySQL, as you can in
any RDBMS without non-standard SQL extensions. I'm working on such a project
at the moment. Do these Oracle things introduce real new functionality or
are they basically syntactic sugar?

 

[Flip]

Howdy.

Well, you can certainly model tree structured data in MySQL, as you can in
any RDBMS without non-standard SQL extensions. I'm working on such a

project
Yup, that's right, it's easy to model it in the table schema. That's what I
was thinking, it's easy to input the data. But it's the sucking it back out
without programming dependency I'm wondering about?

at the moment. Do these Oracle things introduce real new functionality or
are they basically syntactic sugar?

I've been told that Oracle's implementation of the connect by prior is what
you call "sugar" but I can't comment on that as I don't really know, sorry.
;< But from a SQL perspective, I can quickly put in a START WITH and
CONNECT By and get the hierarchical tree of data. In the past, with
SQLServer let's say, I've had to parse the tree programmatically. :< I was
wondering if there was a more general, SQL way to solve this with MySQL.

 

[P.Hill]

I know this might not be the best place for this, but I don't have access to
the MySQL newsgroups here. :<

simple suggestion: comp.lang.java.databases

 

[Adam Jenkins]

Howdy.



project
Yup, that's right, it's easy to model it in the table schema. That's what I
was thinking, it's easy to input the data. But it's the sucking it back out
without programming dependency I'm wondering about?

In fact, Oracle's extension doesn't change that, it just gives you a
nice way to retrieve a tree of data with a single query.

I've been told that Oracle's implementation of the connect by prior is what
you call "sugar" but I can't comment on that as I don't really know, sorry.
;< But from a SQL perspective, I can quickly put in a START WITH and
CONNECT By and get the hierarchical tree of data. In the past, with
SQLServer let's say, I've had to parse the tree programmatically. :< I was
wondering if there was a more general, SQL way to solve this with MySQL.

There is no standard way to do "START WITH/CONNECT BY" with a single SQL
query. That's why Oracle created that extension. However you can do
the same thing in a standard way by executing a series of SQL queries in
a loop.

 

[Flip]

simple suggestion: comp.lang.java.databases
AH! :> That one I have access to! :> Much appreciated! :> Thanks.

 

[Tim Ward]

There is no standard way to do "START WITH/CONNECT BY" with a single SQL
query. That's why Oracle created that extension. However you can do
the same thing in a standard way by executing a series of SQL queries in
a loop.

Ah yes. I did wonder why they were teaching us about calculating transitive
closures of sparse connectivity matrices at college - what use would that
ever be?? Answer - it's the loop of SQL you use to pull out a tree structure
(although for some reason they didn't teach it in SQL!!!).