basic sql query doubt

[Maan Suraj]

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

select * from relationship r1, relationship r2 
where r1.Parent_node=r2.Parent_node and r1.child_node=r2.child_node and r1.relationship=r2.relationship
and r1.child_node=r2.parent_node

[Wendy Gibbons]

have you tried running this sql? what results did you get?

[Martin Vajsar]

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]

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

select * from relationship r1, relationship r2 
where r1.Parent_node=r2.Parent_node and r1.child_node=r2.child_node and r1.relationship=r2.relationship
[b]and r1.child_node=r2.parent_node[/b]

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]

Assuming you're using Oracle, did you look into START WITH and CONNECT BY?