Re: Network Example: Sibling of Opposite Gender

From: Cimode <cimode_at_hotmail.com>
Date: 30 Dec 2006 08:14:44 -0800
Message-ID: <1167495284.551952.127410_at_a3g2000cwd.googlegroups.com>

While I consider that responding to people like Neo is a waste of time, I will adress his 2 stupid challenges with correct solutions, namely:

Based on the following hierarchy
--> Adam son of no one
--> John son of Adam
--> Mary daughter of Adam
--> Jack son of Adam

> Establish a SQL query returning John's brothers AND sisters without involving Adam > Establish a SQL query returning John's brothers OR sisters based on his sex

The structure used is the following (note that I use a surrogate key <<id>> therefore cardinality 1:1 should be implemented between id and concatenated key <<name+sex>>)

create table people
(
id int not null,
name varchar(30) not null,
sex varchar(10) not null
)
go

insert people
select 1, 'Adam', 'Male'
insert people
select 2, 'John', 'Male'
insert people
select 3, 'Jack', 'Male'
insert people
select 4, 'Mary', 'Female'

create table link
(

parent int not null,
child int not null
)
go

insert link
select 1, 2
insert link
select 1, 3
insert link
select 1, 4
go

--Getting John brothers and sisters whithout involving Adam
select P3.name, P3.sex
from people P2

	inner join link L2
		on P2.id = L2.parent
	inner join people P3
		on P3.id = L2.child
	inner join (
				select L1.parent from link L1 inner join people P1
                on L1.child = P1.id where P1.name = 'John'
				) B
		on P2.id = B.parent

where P3.name <> 'John'

--returns...

Jack,	Male
Mary,	Female

--verification using Jack(changing one parameter 'John' to 'Jack')
select P3.name, P3.sex
from people P2

	inner join link L2
		on P2.id = L2.parent
	inner join people P3
		on P3.id = L2.child
	inner join (
				select L1.parent from link L1 inner join people P1
                on L1.child = P1.id where P1.name = 'Jack'
				) B
		on P2.id = B.parent

where P3.name <> 'Jack'

--> returns

John,	Male
Mary,	Female

--Getting ANY brother OR sister to any sibling of opposite sex
 (example with John) --> should return Mary Only select P3.name, P3.sex
from people P2

	inner join link L2
		on P2.id = L2.parent
	inner join people P3
		on P3.id = L2.child
	inner join (
				select L1.parent from link L1 inner join people P1
                on L1.child = P1.id where P1.name = 'John'
				) B
		on P2.id = B.parent
	inner join  (
				select P1.sex from link L1 inner join people P1
                on L1.child = P1.id where P1.name = 'John'
				) C
		on P3.sex <> C.sex

where P3.name <> 'John'

--> returns

Mary, Female
--> only females

--verification using chaging parameter 'John' to 'Mary'

select P3.name, P3.sex
from people P2

	inner join link L2
		on P2.id = L2.parent
	inner join people P3
		on P3.id = L2.child
	inner join (
				select L1.parent from link L1 inner join people P1
                on L1.child = P1.id where P1.name = 'Mary'
				) B
		on P2.id = B.parent
	inner join  (
				select P1.sex from link L1 inner join people P1
                on L1.child = P1.id where P1.name = 'Mary'
				) C
		on P3.sex <> C.sex

where P3.name <> 'Mary'

--returns

John,	Male
Jack,	Male

--> ONLY Males

Case closed...I have solved the second challenge simply by using an additional join.
Also you should keep in mind that NO NULL value should be allowed in the first place. Any solution involving NULLS is not 1NF and therefore is a hack... Received on Sat Dec 30 2006 - 17:14:44 CET