Re: Hierarchical relationship?
Posted by: Peter Brawley
Date: October 05, 2013 12:24PM

samples (sid pk,name,...)
compounds (cid pk, sid, foreign key(sid) references samples(sid),...)
props (pid pk, cid, foreign key(cid) references compounds(cid),...)

--- find props for sample IDs 1,5,10:
select p.pid
from props p
join compounds c using(cid)
where p.sid in(1,5,10);

--- find props for samples named 'foo', 'bar'
select p.pid
from props p
join compounds c using(cid)
join samples s using(sid)
where s.name in('foo', 'bar');

Repeating foreign keys down the chain is most always a design error.

Options: ReplyQuote


Subject
Written By
Posted
September 27, 2013 09:33AM
September 27, 2013 11:14AM
September 27, 2013 11:22AM
Re: Hierarchical relationship?
October 05, 2013 12:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.