SybaseBlog

QUESTION:

Hello Techies,

Can you let me know on which situation the below type of queries are used.

SELECT f1.parent_id , f2.child_id
FROM PortfolioTreeFlat f1 , PortfolioTreeFlat f2
WHERE f1.child_id = Pparent_id and f2.parent_id = Pchild_id ;

Is this an "exclusive or"?

ANSWER:

- Given a child and a parent,
- Give me all parents of that child,
- Crossed with all children of that parent

Basically, get all the children of this parent, and all the parents of this child, and then present it to me in every possible combination of the two.

create table atable (parent_id int, child_id int)
go

insert into atable (parent_id, child_id) values (1,4)
go
insert into atable (parent_id, child_id) values (1,5)
go
insert into atable (parent_id, child_id) values (2,4)
go
insert into atable (parent_id, child_id) values (3,5)
go

select a.parent_id, b.child_id
from atable a, atable b
where a.child_id = 4 and b.parent_id = 1
go

1 4 "this is us"
1 5 "this is parent's other child"
2 4 "this is child's other parent"
2 5 "this is child's parent with the parent's child"

This is not an "EXCLUSIVE OR". In an exclusive OR each row would have either a parent of the child or the child of the parent, but not both. So the first row would not show up, neither would the 3rd.

This is just an "OR". That is, give me any combination where there is a parent of the given child, OR there is a child of the given parent.

I can not really think of a business reason why this is needed.