Re: Help writing SQL query

There are actually several ways to do this, but here is one using CONNECT BY:

SELECT DISTINCT familymember1
FROM family
CONNECT BY PRIOR familymember2 = 'Brian' START WITH familymember1 = 'Brian'
UNION
SELECT DISTINCT familymember2
FROM family
CONNECT BY PRIOR familymember2 = 'Brian' START WITH familymember1 = 'Brian'
UNION
SELECT DISTINCT familymember1
FROM family
CONNECT BY PRIOR familymember1 = 'Brian' START WITH familymember2 = 'Brian'

Again, you only get the generation directly linked to Brian, which can be done without the CONNECT BY anyway. Maybe you can adapt it, but I seriously suspect this must be done programmatically.

"Barry Bulsara" <bbulsara23_at_hotmail.com> wrote in message news:1102453770.27168.0_at_damia.uk.clara.net...
> I would be interested in seeing the query Alan, yes please do post.
>
> I was initially loathe to use set operators like UNION but in the end
> couldn't see a way around not using them. Although I haven't seen your
query
> yet, there does seem commonality in the way we are thinking. I keen too to
> see whether your query gives the same resultset if the query is modified
to
> START WITH any member of a given family (ie get the same resultset if
> queried for Brian, Mary, Francis, Bruce etc).
> Thanks in advance
> Barry
>
>
>
> "Alan" <alan_at_erols.com> wrote in message
> news:31mjr2F3bp95oU1_at_individual.net...
> >I was able to write a query using UNION and MINUS to get correct results
> >for
> > one generation only. I can re-create it and post it if it would help,
but
> > I
> > really think the only solution is procedural.
> >
> >
> > "Barry Bulsara" <bbulsara23_at_hotmail.com> wrote in message
> > news:1102447852.5178.0_at_lotis.uk.clara.net...
> >> I will try to work with your suggestion. Thank you for your pointers.
> >>
> >> Up to now my gut feeling is that I can do this with Oracle hierarchical
> >> queries. My SQL below is what I have been working on. It is bugged
> > ('almost'
> >> gives me the required results) but I think I am almost there.
> >> Thank you
> >> Barry
> >>
> >> SQL> SELECT DISTINCT f1.familymember1 AS familymember
> >> 2 FROM family f1,family f2
> >> 3 CONNECT BY NOCYCLE PRIOR f1.familymember1=f2.familymember2
> >> 4 START WITH f1.familymember1='Brian'
> >> 5 UNION
> >> 6 SELECT DISTINCT f4.familymember1 AS familymember
> >> 7 FROM family f3,family f4
> >> 8 CONNECT BY NOCYCLE PRIOR f4.familymember1=f3.familymember2
> >> 9 START WITH f4.familymember1='Brian';
> >>
> >> FAMILYMEMBER
> >> --------------------
> >> Brian
> >> Claudine
> >> Dominic
> >> Elizabeth
> >> Fergie
> >> Francis
> >> Jean-Claude
> >> John Francois
> >> Mary
> >> Peter
> >>
> >> 10 rows selected.
> >>
> >> SQL>
> >> SQL> SELECT * FROM v$version;
> >>
> >> BANNER
> >> ----------------------------------------------------------------
> >> Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
> >> PL/SQL Release 10.1.0.2.0 - Production
> >> CORE 10.1.0.2.0 Production
> >> TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
> >> NLSRTL Version 10.1.0.2.0 - Production
> >>
> >> SQL>
> >>
> >>
> >>
> >>
> >>
> >> "GQ" <dbaguy_ott_at_yahoo.com> wrote in message
> >> news:1102441644.576437.298720_at_f14g2000cwb.googlegroups.com...
> >> > This is as far as I got, until you had a circular reference, which
> >> > created an endless loop
> >> > in the recursive code ... (Brian -> Mary -> Peter ->Brian...)
> >> >
> >> > create or replace function get_assoc(p_val in varchar2, p_ref in
> >> > varchar2) return varchar2 is
> >> > cursor c_val is
> >> > select distinct decode(p_val,familymember1,
> >> > familymember2,familymember1)
> >> > from family
> >> > where familymember1 = p_val
> >> > or familymember2 = p_val;
> >> > v_rel_name varchar2(30);
> >> > v_ref_name varchar2(30);
> >> > v_relatives varchar2(2000) := ' ';
> >> > begin
> >> > v_ref_name := p_ref;
> >> > open c_val;
> >> > fetch c_val into v_rel_name;
> >> > while c_val%found loop
> >> > if v_rel_name != p_ref then
> >> > if length(v_relatives) < 2 then
> >> > v_relatives := p_val ||', '|| v_rel_name; -- ||
> >> > get_assoc(v_rel_name,v_ref_name);
> >> > else
> >> > v_relatives := v_relatives ||', '|| v_rel_name; -- ||
> >> > get_assoc(v_rel_name,v_ref_name);
> >> > end if;
> >> > end if;
> >> > fetch c_val into v_rel_name;
> >> > end loop;
> >> > return v_relatives;
> >> >
> >> > exception
> >> > when no_data_found then return v_relatives;
> >> > end;
> >> > /
> >> > select get_assoc('Brian','Nobody else') from dual;
> >> > ...produces
> >> > Brian, Claudine, Mary, Peter
> >> >
> >>
> >>
> >
> >
>
>
Received on Wed Dec 08 2004 - 09:36:20 CST