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
> So the relations are like a hierarchical tree:
> 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?.
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
Here's an article on how it's used in Oracle - is this what you need?
BTW - MySQL people - any idea when this will be implemented? I have a
couple of projects with hierarchical records which would just love this