Re: Help writing SQL query

barry wrote:
> There was no red herring here. I posted the design, only the data
(the
> peoples names) was contrived. FYI the real scenario relates to a
chemical
> database being migrated from Access to Oracle. The pairs of names in
the
> family table are really pairs of atom numbers in a molecule. The
> relationship between them, two being on the same row in the table,
> represents a bond. I didn't put in the chemistry model as then I
would have
> to explain how the connectivity is represented for SP3, SP2, SP,
tautomeric
> bonds and salts etc which is not trivial. And there is much other
data
> irrelevant to the problem at hand too. My family model is much
simpler; the
> audience of this group will not be and do not have to be familiar
with
> chemistry.

To address your actual problem (a radical idea I guess!), I can't see any way to do this in pure SQL. There may well be such a way, but it eludes me. A brute force approach would be to create a table like this:

create table family_member (family_id, name);

Now process the old data like this:

for each row:
is there a row in family_member where name=familymember1? if yes then
is there a row in family_member where name=familymember2? if yes then
do both family_member rows have same family_id? if no then
-- merge the 2 families
update family_member
set family_id = familiy_id1
where family_id = familiy_id2;
end if
else
insert into family_member(family_id1,familymember2) end if
else
is there a row in family_member where name=familymember2? if yes then
insert into family_member(family_id2,familymember1) else
Create new family_id
insert into family_member(family_id,familymember1) insert into family_member(family_id,familymember2) end if
  end if

Crude, but it just might work! Received on Wed Dec 08 2004 - 04:29:41 CST