Sql program

[subhransu123]

There r two table .....
Person(pid,name,city)
parent(parentid,childid)
both parentid and childid are foreign key refering to person.pid


1. Find the name of gradparents of all people whose city name is kolkata.
2. Find the name of all people who lived in the same city as their father.


Please help to solve it.........

[Vyom]

How can both parentid and childid be a foreign key refering to a single attribute, person.pid?

[subhransu123]

Yes it is true ........this question is given in msc question .

[manaskumar]

Originally Posted by subhransu123

There r two table .....
Person(pid,name,city)
parent(parentid,childid)
both parentid and childid are foreign key refering to person.pid


1. Find the name of gradparents of all people whose city name is kolkata.
2. Find the name of all people who lived in the same city as their father.


Please help to solve it.........

Hope this works!!

Answer 1:

select p.name
from Person p, parent pr, parent pr1
where pr.childid = pr1.parentid
	AND pr.parentid = p.pid
	AND p.city = "kolkalta";

Answer 2:

Select ch.name
from (select pr.parentid,pr.childid, p.city
	from parent pr, person p
	where pr.parentid = p.pid) fa,

	(select pr.parentid,p.name, pr.childid, p.city
	from parent pr, person p
	where pr.childid = p.pid) ch
where fa.parentid = ch. parentid AND fa.city = ch.city;

[RazorbladeXtreme]

Originally Posted by vineet369

How can both parentid and childid be a foreign key refering to a single attribute, person.pid?

Yes, it is possible, the foreign key may even reference a column from same table.

Originally Posted by Wikipedia

The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in SQL:2003 as a self-referencing or recursive foreign key.

[subhransu123]

many many thanks to manaskumar...........

[Liverpool_fan]

Originally Posted by vineet369

How can both parentid and childid be a foreign key refering to a single attribute, person.pid?

Of course it can. It doesn't mean both of the relations has to be with the same tuple.

Originally Posted by manaskumar

Hope this works!!

Answer 1:

select p.name
from Person p, parent pr, parent pr1
where pr.childid = pr1.parentid
	AND pr.parentid = p.pid
	AND p.city = "kolkalta";

Answer 2:

Select ch.name
from (select pr.parentid,pr.childid, p.city
	from parent pr, person p
	where pr.parentid = p.pid) fa,

	(select pr.parentid,p.name, pr.childid, p.city
	from parent pr, person p
	where pr.childid = p.pid) ch
where fa.parentid = ch. parentid AND fa.city = ch.city;

Glad that you helped the OP, but the point is the OP won't learn it this way with answer offered on the plate.

[vickybat]

@ Op

Manaskumar gave you an example of subqueries. You should read more SQL material to learn more.

In the above example, both parentid and childid referred to the primary key i.e pid. So a relation had to be made between parentid and childid in order to pick corresponding data. Using pid, which is foreign key to both parentid and childid, a relation is made between them.

Refer your second question. In the first subquery,manas picked parentid,childid and city from both tables satisfying the join condition where parentid and pid are equal and rest are filtered.
Similarly in the 2nd subquery, the join condition was childid = pid. So rest of the data are filtered.

Now you got all the childid and parentid which are equal to pid and thus a relation between them is formed. Finally you pick name of child satisfying the above condition and an extra AND condition is given to match the city name.

[Zangetsu]

^^oh yeah what Manaskumar posted is correct..
here he used inner join + self join methodology

I remember similar type of question in interview
"Employee Table with data of both normal employees & manager...
write the query to find list of managers..."
ans is self join...

[RazorbladeXtreme]

Answer 1 isn't fully correct. It won't give the names of the grandparents if they didn't live in Kolkata. If a person lives in Kolkata, and his grandparents' data exist, it should display it.

EDIT:

select p2.name
from Person p, Person p2, parent pr, parent pr1
where pr.childid = pr1.parentid
	AND pr1.childid = p.pid
	AND p.city = 'Kolkata'
        AND p2.pid = pr.parentid;

I edited manaskumar's query and think this might be correct, but looks very unoptimised. I'll try something from scratch later (Have Microprocessor exam tomorrow ).