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.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
