Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 35A102E2B91 for ; Wed, 14 Nov 2007 08:57:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 80875-10 for ; Wed, 14 Nov 2007 08:57:29 -0400 (AST) Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 4B13F2E266B for ; Wed, 14 Nov 2007 08:57:33 -0400 (AST) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Wed, 14 Nov 2007 13:55:19 +0100 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Wed, 14 Nov 2007 13:57:30 +0100 Message-Id: <473AFEC1.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Wed, 14 Nov 2007 13:57:21 +0100 From: "Bart Degryse" To: Subject: Re: ALL() question References: <1195047542.3190.9.camel@frodon.be-bif.ulb.ac.be><473AE27E.60501 03@archonet.com><1195051800.3190.18.camel@frodon.be-bif.ulb.ac.be> In-Reply-To: <1195051800.3190.18.camel@frodon.be-bif.ulb.ac.be> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part446234A1.0__=" X-imss-version: 2.049 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/66 X-Sequence-Number: 29594 --=__Part446234A1.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable The doc says "The right-hand side is a parenthesized subquery, which must = return exactly one column..." That's what you have if using "... where test_bit_id =3D all(select id = from test_bits where id in (1,2,3,4));" The doc continues "...The left-hand expression is evaluated and compared = to each row of the subquery result using the given operator, which must = yield a Boolean result ..." So your where expression is equivalent to: where test_bit_id =3D (select id from test_bits where id =3D 1) AND test_bit_id =3D (select id from test_bits where id =3D 2) AND test_bit_id =3D (select id from test_bits where id =3D 3) AND test_bit_id =3D (select id from test_bits where id =3D 4); The doc continues "... The result of ALL is "true" if all rows yield true = ..." Since test_bit_id can never be 1, 2, 3 and 4 at the same time the result = of ALL will be false. So no records get returned. >>> Julien Cigar 2007-11-14 15:50 >>> On Wed, 2007-11-14 at 11:56 +0000, Richard Huxton wrote: > Julien Cigar wrote: > >=20 > > What I would like is a query that returns all the specimen_id of=20 > > this table which have _all_ the given test_bit_id.=20 > [snip] > > With the following I got a syntax error: > > select specimen_id=20 > > from specimen_test_bits=20 > > where test_bit_id =3D all(1,2,3,4); >=20 > It's expecting an array here. You'd have to write > =3D all('{1,2,3,4}') > But that would have the same problem as... >=20 > > The following works but no rows are returned : > > select specimen_id=20 > > from specimen_test_bits=20 > > where test_bit_id =3D all(select id from test_bits where id in = (1,2,3,4)); >=20 > It's testing each row individually and of course one row can't match = ALL=20 > four values. >=20 > What you want to do is count the distinct values. Something like: >=20 > SELECT > specimen_id > FROM foo > GROUP BY > specimen_id > HAVING > count(distinct test_bit_id) =3D 4 > ; >=20 I don't think it would work, for example if I have: specimen_id | test_bit_id ------------+------------ 100 1 100 3 101 1 101 2 the test_bit_ids are parameters, so with the given test_bit_id 1,3 it would return specimen_id 101 too, which I don't want ... What I would like is the specimen_id which match _exactly_ the given test_bit_ids, so it should return only 100 in this example .. from the documentation ALL() can take a subquery too, not only an ARRAY (http://www.postgresql.org/docs/8.2/static/functions-subquery.html) ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate --=__Part446234A1.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
The doc says "The right-hand side is a parenthesized subquery, which = must return exactly one column..."
That's what you have if using "... where test_bit_id =3D all(select = id from test_bits where id in (1,2,3,4));"
The doc continues "...The left-hand expression is evaluated and = compared to each row of the subquery result using the given operator, = which must yield a Boolean result ..."
So your where expression is equivalent to:
where test_bit_id =3D (select id from test_bits where id =3D 1) = AND
          test_bit_id = =3D (select id from test_bits where id =3D 2) AND
   &nbs= p;      test_bit_id =3D (select id from = test_bits where id =3D 3) AND
       =    test_bit_id =3D (select id from test_bits where id =3D = 4);
The doc continues "... The result of ALL is "true" if all rows = yield true ..."
Since test_bit_id can never be 1, 2, 3 and 4 at the same time the = result of ALL will be false. So no records get returned.


>>> Julien Cigar <jcigar@ulb.ac.be> 2007-11-14 = 15:50 >>>

On Wed, 2007-11-14 at 11:56 +0000, Richard = Huxton wrote:
> Julien Cigar wrote:
> >
> > What = I would like is a query that returns all the specimen_id of
> > = this table which have _all_ the given test_bit_id.
> [snip]
> = > With the following I got a syntax error:
> > select = specimen_id
> > from specimen_test_bits
> > where = test_bit_id =3D all(1,2,3,4);
>
> It's expecting an array = here. You'd have to write
>   =3D all('{1,2,3,4}')
> = But that would have the same problem as...
>
> > The = following works but no rows are returned :
> > select specimen_id =
> > from specimen_test_bits
> > where test_bit_id =3D = all(select id from test_bits where id in (1,2,3,4));
>
> It's = testing each row individually and of course one row can't match ALL =
> four values.
>
> What you want to do is count the = distinct values. Something like:
>
> SELECT
>  = ;  specimen_id
> FROM foo
> GROUP BY
>  &n= bsp; specimen_id
> HAVING
>    count(distinct = test_bit_id) =3D 4
> ;
>

I don't think it would work, = for example if I have:
specimen_id | test_bit_id
------------+-------= -----
   100       1
 &n= bsp; 100         3
   = 101         1
   = 101         2

the test_bit_i= ds are parameters, so with the given test_bit_id 1,3 it
would return = specimen_id 101 too, which I don't want ...
What I would like is the = specimen_id which match _exactly_ the given
test_bit_ids, so it should = return only 100 in this example ..

from the documentation ALL() can = take a subquery too, not only an ARRAY
(http://www.postgresql.org/docs/8= .2/static/functions-subquery.html)




---------------------= ------(end of broadcast)---------------------------
TIP 7: You can help = support the PostgreSQL project by donating at

   &nbs= p;            http://www.postgresql.org/a= bout/donate
--=__Part446234A1.0__=--