Win a copy of Beginning Java 17 Fundamentals: Object-Oriented Programming in Java 17 this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

basic sql query doubt

 
Ranch Hand
Posts: 299
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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







 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
have you tried running this sql? what results did you get?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 L Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 344
Oracle Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Assuming you're using Oracle, did you look into START WITH and CONNECT BY?
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic