wood burning stoves 2.0*
The moose likes JDBC and the fly likes basic sql query doubt Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "basic sql query doubt" Watch "basic sql query doubt" New topic
Author

basic sql query doubt

Maan Suraj
Ranch Hand

Joined: Dec 20, 2007
Posts: 299
I have a table structure like below

Table name :"relationship"
Columns of above table are
Parent_node, child_node,relatiohsip,Desc,other values .

The Primary key is a combination of "Parent_node","Relationship","child_node". Now say there are 2 ids given (IDs are values of "parent_node","child_node").Lets say, the id given is "100","200". I have to look up in the relationship table and find out the relationship between "100" and "200". Lets assume 100 is parent of 200."200" in turn can be parent of "300".another input sayis "100", "300", i need to still find out that 100 is parent (or say grand parent) of 300.

can below sql query correct/ improved








Man Suraj
Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

have you tried running this sql? what results did you get?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Some databases might offer better (nonstandard) support for this kind of queries. If you're on Oracle, you might use either hierarchical queries or (maybe) analytic functions for this, depending on your exact needs.

I don't see a direct way to improve your query, unless you could limit the number of indirect parents a child can have. I think database-agnostic SQL won't let you get the general answer without further (procedural) processing, but I may be mistaken, as I always use hierarchical queries for this and I didn't ever need to investigate ANSI SQL approach.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Maan Suraj wrote:I have a table structure like below

Table name :"relationship"
Columns of above table are
Parent_node, child_node,relatiohsip,Desc,other values .

The Primary key is a combination of "Parent_node","Relationship","child_node". Now say there are 2 ids given (IDs are values of "parent_node","child_node").Lets say, the id given is "100","200". I have to look up in the relationship table and find out the relationship between "100" and "200". Lets assume 100 is parent of 200."200" in turn can be parent of "300".another input sayis "100", "300", i need to still find out that 100 is parent (or say grand parent) of 300.

can below sql query correct/ improved








I don't think it will work, as both parents have to match and both children have to match, then the parents and children have to match, all in one row
Koen Aerts
Ranch Hand

Joined: Feb 07, 2012
Posts: 344

Assuming you're using Oracle, did you look into START WITH and CONNECT BY?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: basic sql query doubt
 
Similar Threads
Collection as a parameter in a Callable Statement
functional dependency of table attributes
SQL question?
Need Help on HTML
first java am stuck