List:General Discussion« Previous MessageNext Message »
From:Steve Folly Date:January 13 2004 6:58pm
Subject:Re: hierarchical records, I need some help!! ;(
View as plain text  
	
On 13 Jan 2004, at 09:19, Victor Reus wrote:

> Hi again,
>
>> Second: specify what you mean with "all relations". Can you
>> share some sample data and sample output (that is: what
>> do you expect the query to return).
>
> Ok my table noms is like
>
>  | id           | int(11)      |      | PRI | [NULL] | auto_increment
>  | reference    | varchar(255) |
>  | component    | varchar(255) |
>
> The relation between refefence and component are like parent -> child
> Here is an example
> Table contains :
>
> ID    REFERENCE  COMPONENT
> 1     A004       B001
> 2     B001       C003
> 3     B001       D003
>
> (I have more records but only this are what i want to take as an 
> example)
>
> So the relations are like a hierarchical tree:
>
> A004
>    BOO1
>       C003
>       D003
>
> And i want one sentence that takes all the relations.
>
> The SQL statement result should be like:
>
> REFERENCE  COMPONENT
> AOO4       B001
> B001       COO3
> B001       COO3
> ....       ....
>
>
> Coudl somebody give me a tip to do it?.
>
> Thanks.
> Victor.


Why not

SELECT * FROM THE_TABLE WHERE REFERENCE = 'B001' OR COMPONENT = 'B001'

This will work given your example.

However, I suspect this isn't the full story. Do you also want to see 
what components make up C003 and D003 in the same query? I think you're 
after a feature not yet implemented in MySQL - the 'CONNECT BY PRIOR' 
SELECT statement, just the ticket for hierarchical queries.

http://www.mysql.com/doc/en/TODO_future.html - the first item in the 
list!

Here's an article on how it's used in Oracle - is this what you need?

http://www.oracleadvice.com/Tips/pkfktree.htm

BTW - MySQL people - any idea when this will be implemented? I have a 
couple of projects with hierarchical records which would just love this 
query!

-- 
Regards,
Steve.

Thread
hierarchical records, I need some help!! ;(Victor Reus12 Jan
  • Re: hierarchical records, I need some help!! ;(Martijn Tonies12 Jan
    • Re: hierarchical records, I need some help!! ;(Victor Reus13 Jan
      • Re: hierarchical records, I need some help!! ;(Steve Folly13 Jan
        • Re: hierarchical records, I need some help!! ;(Jochem van Dieten13 Jan
          • Re: hierarchical records, I need some help!! ;(Steve Folly14 Jan
        • RE: hierarchical records, I need some help!! ;(Matt Griffin13 Jan
  • Re: hierarchical records, I need some help!! ;(Mike13 Jan