Re: Need help with a complicate sql select request

From: bozon <curtis_at_crowson1.com>
Date: 7 Jul 2006 07:36:55 -0700
Message-ID: <1152283015.835980.76690@s13g2000cwa.googlegroups.com>

My head hurts now but here "A" query, I thought of another way I might be able to do it but I didn't want to work it out (I don't really have the time.)

create table tab_a(
  mgid char(4),
  gid char(4)
) ;

create table tab_b(
  uid char(4),
  gid char(4)
) ;

insert into tab_a values ("mg1", "grp1") ;
insert into tab_a values ("mg1", "grp2") ;
insert into tab_a values ("mg2", "grp3") ;

insert into tab_b values ("usr1", "grp1") ;
insert into tab_b values ("usr1", "grp2") ;

insert into tab_b values ("usr2", "grp1") ; insert into tab_b values ("usr2", "grp3") ; }

• Test query to see how it works. select b1.uid, multiset( select b2.gid from tab_b b2 where b2.uid = b1.uid ), multiset( select a2.gid from tab_a a2 where a2.mgid = a1.mgid) from tab_b b1, tab_a a1 where a1.gid = b1.gid ;
• Final query. select distinct b1.uid from tab_b b1, tab_a a1 where a1.gid = b1.gid and multiset( select b2.gid from tab_b b2 where b2.uid = b1.uid ) = multiset( select a2.gid from tab_a a2 where a2.mgid = a1.mgid) ;

I hope this works in all cases. The logic would seems to work. I suspect it might be slow. ;-)

Thorsten Knopel wrote:
> Sorry corrected value in Table B
>
> Thorsten Knopel wrote:
> > hello , i need some help for a complicate sql search, hope someone can
> > help me
> >
> >
> > Problem:
> >
> > Table A: mega_grp, grp
> > ----------------------
> >
> > e.g. mg1, grp1
> > mg1, grp2
> > mg2, grp3
> > -----------------------
> >
> > Table B: user, grp
> > ----------------------
> > e.g. usr1, grp1
> > usr1, grp2
> > usr2, grp1
> > usr2, grp3
> > ----------------------
> >
> >
> > What we want is to find all user which are in mega_group "mg1". That
> > mean all user which are in "grp1" and "grp2". Not only in one but in
> > both. In the example it would be only "usr1" because "usr2" is not in
> > "grp2"
> >
> >
> > I tried it with a subselect and an IN Operation but then i get all user
> > which are in one of the groups but not in both.
> >
> >
> > Thanxs for your help
> >
> > Thorsten
Received on Fri Jul 07 2006 - 09:36:55 CDT