Need help with a complicate sql select request
The poster needed SQL to find users belonging to *all* groups of a given mega-group (two tables: mega_grp/grp and user/grp); his IN-subquery returned users in any one group instead. This is a classic "relational division" problem. Suggestions included a MULTISET comparison of each user's groups against the mega-group's groups, and the cleaner, generally accepted answer: select from B where grp IN (groups of 'mg1') GROUP BY user HAVING COUNT(*) = (SELECT COUNT(*) FROM A WHERE mega_grp='mg1'). A further nested-count variant from Jonathan Leffler was tested and reported as not giving the right answer. Joe Celko's book was cited for more division techniques; the original poster never confirmed which solution he used.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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, grp1 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
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
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
from wht i think.. your need is tht it should list all the users
depending on the mega group name you giv..
so i think this should work..
SELECT b.user
FROM b
WHERE b.grp IN
(
SELECT a.grp
FROM a
WHERE a.mega_grp = 'mg1'
);
In article <e8lmm4$icd$1@pentheus.materna.de>,
Thorsten Knopel <to_stupid@gmx.de> wrote:
> hello , i need some help for a complicate sql search, hope someone can help
> me
[data snipped]
>
> 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"
Sort of a "relational division" problem. One way is something like this,
assuming that you don't have duplicates in table B:
select user
from B
where grp in (select grp from A where mega_grp = 'mg1')
group by user
having count(user) = (select count(*) from A where mega_grp = 'mg1')
didn't try it to make sure but something like that ought to do it.
You can also do it with a double NOT IN, which I am too lazy at
the moment to try to recall...
Karl
Karl Schendel wrote:
> In article <e8lmm4$icd$1@pentheus.materna.de>,
> Thorsten Knopel <to_stupid@gmx.de> wrote:
>
> > hello , i need some help for a complicate sql search, hope someone can help
> > me
> [data snipped]
> >
> > 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"
>
> Sort of a "relational division" problem. One way is something like this,
> assuming that you don't have duplicates in table B:
> select user
> from B
> where grp in (select grp from A where mega_grp = 'mg1')
> group by user
> having count(user) = (select count(*) from A where mega_grp = 'mg1')>
> didn't try it to make sure but something like that ought to do it.
>
> You can also do it with a double NOT IN, which I am too lazy at
> the moment to try to recall...
>
> Karl
Joe Celko's SQL for Smarties has a nice chapter on relational division.
If my above query is too slow for your needs then look at Joe's chapter
on relational division. Of course he warns that his queries are slow,
also.
On 7/7/06, Thorsten Knopel <to_stupid@gmx.de> wrote:
> Sorry corrected value in Table B
>
> Thorsten Knopel wrote:
> > hello , i need some help for a complicate sql search, [...]
> >
> > 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"
Valid users for each mega-group have as many entries in table B with a
group listed in table A for the current mega-group as there are
entries in table A for the current mega-group...
select distinct b1.user, a1.mega_grp from a as a1, b as b1
where a1.user in
(select b2.user from b as b2
where
(select count(*) from a as a2 where a1.mega_grp = a2.mega_grp) =
(select count(*) from a as a3, b as b3
where a3.mega_grp = a1.mega_grp and a3.grp = b3.grp)
)
I've not tested this - so it may not be syntactically correct. If
there's a semantic problem, it will be comparing the two inner-most
sub-queries, I think.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
When I test this (I was curious) I don't get the right answer. It also
has one typo I believe,
which is "where a1.user in" There isn't a user in the a table I changed
a1 to b1 but it gave me both users.
I'll look at it closer when I get a chance to see where the train
jumped the track, but I have 5 things going on today that are
absolutely due yesterday.
Jonathan Leffler wrote:
> On 7/7/06, Thorsten Knopel <to_stupid@gmx.de> wrote:
> > Sorry corrected value in Table B
> >
> > Thorsten Knopel wrote:
> > > hello , i need some help for a complicate sql search, [...]
> > >
> > > 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"
>
> Valid users for each mega-group have as many entries in table B with a
> group listed in table A for the current mega-group as there are
> entries in table A for the current mega-group...
>
> select distinct b1.user, a1.mega_grp from a as a1, b as b1
> where a1.user in
> (select b2.user from b as b2
> where
> (select count(*) from a as a2 where a1.mega_grp = a2.mega_grp) =
> (select count(*) from a as a3, b as b3
> where a3.mega_grp = a1.mega_grp and a3.grp = b3.grp)
> )>
> I've not tested this - so it may not be syntactically correct. If
> there's a semantic problem, it will be comparing the two inner-most
> sub-queries, I think.
>
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler@earthlink.net, jleffler@us.ibm.com
> Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/