Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

basic sql query doubt

 
Maan Suraj
Ranch Hand
Posts: 299
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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







 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
have you tried running this sql? what results did you get?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 344
Java Linux Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Assuming you're using Oracle, did you look into START WITH and CONNECT BY?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic