I'm not sure but the situation you are describing sounds like the so-called
"Bill of Materials" Problem, sometimes called BOM for short.
I'm certain that some databases provide SQL to handle BOM problems; for
example, DB2 which I know quite well, provides for BOMs. However, I just did
a search in the MySQL 5.0 manual and could not find a single hit on "Bill of
Materials" or "BOM" so I suspect that MySQL does not support this, although
it might some day. Someone once told me about another database that supports
BOMs; it might have been Oracle but I can't be sure. He and I discussed BOMs
and I showed him the SQL used by DB2 to handle them; he said the SQL for
BOMs in the other database was quite different but didn't show it to me.
In short, I suspect that solving your problem in MySQL will be difficult or
maybe impossible. Solving it in DB2 or some other database should be
possible if using another database is an option for you.
But don't give up yet! I may have misunderstood your requirement and it
really isn't a BOM at all: after all, a BOM usually proceeds downwards from
parents to children to grandchildren but you seem to want to go upwards;
that may require a somewhat different approach.
I just did a search on "Bill of Materials" in the MySQL archives and found
some useful information, particularly this article by Peter Brawley:
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#parts_explosion
It describes a way to do a BOM (also known as a "Parts Explosion") in MySQL
and also gives many links to articles about BOMs which might be helpful to
you.
Also, if you do a Google search on "Bill of Materials MySQL" in Google the
way I just did, you'll find over 200,000 hits, some of which will point to
tools or techniques that might help you with your problem.
--
Rhino
----- Original Message -----
From: "VenuGopal Papasani" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, March 02, 2006 5:24 AM
Subject: help regarding writing a query
HI all,
This is venugopal.Being a silent member of this group.I am having a doubt
regarding writing a query.I can explain it with an example
Consider i have a table called GetParents
The table consists of two fieds called num as Integer
ParentNum as Integer
The table consists of the following data
num ParentNum
1 4
2 1
3 5
6 7
4 2
Now the output should be as follows if i have given input as 4
then output should be as
num ParentNum
1 4
2 1
4 2
Explaination of Output:
As the given input is 4.Input is compared with num and we get 4->
2.Now ParentNum is taken and checked in the table for 2 being the num and
compared and 2->1 is obtained and now again the comparision is taken and
1->2 is given as ouput
NOw the problem.Can we get the result by writing only one query or we
have to write more no of queries.If it is possible to get this with one
query then can u please give me the query.
Can u give me the query or tell me the source where i can get examples
of such queries.
It will be very much helpful for me.
Thanks in advance,
Regards,
venu.
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
