RE: Need help with a complicate sql select request
This would give the results as specified....If I understand the request correctly.
select distinct b1.user, a1.mega_grp
from a as a1, b as b1
where a1.mega_grp = 'mg1'
AND a1.grp in (select b2.grp from b as b2
where (select count(*) from a as a2 where a2.mega_grp = a1.mega_grp) =
(select count(*) from a as a3, b as b3 where a3.mega_grp = a1.mega_grp AND b3.grp = a3.grp AND b3.user = b1.user)
);
Regards
TJ
-----Original Message-----
From: bozon [mailto:curtis@crowson1.com]
Sent: Tuesday, July 11, 2006 3:05 PM
To: informix-list@iiug.org
Subject: Re: Need help with a complicate sql select request
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/
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
***********************************************************************************************
This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission.
This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems.
***********************************************************************************************