Обсуждение: ALL() question

Hello,

I have a problem with the ALL() subquery expression.
I have three tables:
- specimens
- test_bits
- specimen_test_bits

The specimen_test_bits table contains two foreign keys, one to
specimens(id), another to test_bits(id). 

Here is an output of specimen_test_bits:

muridae=> select * from specimen_test_bits;specimen_id | test_bit_id 
-------------+-------------      46096 |           1      46096 |           2      46096 |           3      46096 |
     4      52894 |           1      52894 |           3      12546 |           2
 

What I would like is a query that returns all the specimen_id of 
this table which have _all_ the given test_bit_id. So in this
case, with test_bit_id 1,2,3,4 it should return only 
specimen_id 46096.

With the following I got a syntax error:
select specimen_id 
from specimen_test_bits 
where test_bit_id = all(1,2,3,4);

The following works but no rows are returned :
select specimen_id 
from specimen_test_bits 
where test_bit_id = all(select id from test_bits where id in (1,2,3,4));

Any idea how I could do this ? I guess the problem is my ALL() expression ...

In advance thanks,

Julien

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 = all(1,2,3,4);

It's expecting an array here. You'd have to write = 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 = 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  specimen_id
HAVING  count(distinct test_bit_id) = 4
;

--   Richard Huxton  Archonet Ltd

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 = all(1,2,3,4);
> 
> It's expecting an array here. You'd have to write
>   = 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 = 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
>    specimen_id
> HAVING
>    count(distinct test_bit_id) = 4
> ;
> 

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)

I finally found a solution:

SELECT specimen_id 
FROM specimen_test_bits 
GROUP BY specimen_id 
HAVING array_accum(test_bit_id) =  '{2,3,4}';

.. but I don't think it's very "clean" ..

what do you think ?

Thanks

On Wed, 2007-11-14 at 15:50 +0100, Julien Cigar wrote:
> 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 = all(1,2,3,4);
> > 
> > It's expecting an array here. You'd have to write
> >   = 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 = 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
> >    specimen_id
> > HAVING
> >    count(distinct test_bit_id) = 4
> > ;
> > 
> 
> 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)
> 
> 

Julien Cigar wrote:
> 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 = all(1,2,3,4);
>> It's expecting an array here. You'd have to write
>>   = 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 = 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
>>    specimen_id
>> HAVING
>>    count(distinct test_bit_id) = 4
>> ;
>>
> 
> 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 ...

Not if you test for what you want too:

...
FROM foo
WHERE test_bit_id = ANY ('{1,3}')
...or...
WHERE test_bit_id IN (1,3)

> 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)

Yes, but that doesn't help with your query - one row can't match ALL 
your values.

--   Richard Huxton  Archonet Ltd

Julien Cigar wrote:
> I finally found a solution:
> 
> SELECT specimen_id 
> FROM specimen_test_bits 
> GROUP BY specimen_id 
> HAVING array_accum(test_bit_id) =  '{2,3,4}';
> 
> .. but I don't think it's very "clean" ..

The key question is whether you can rely on getting (2,3,4) or whether 
you might get (4,3,2) or some other ordering.

--   Richard Huxton  Archonet Ltd

On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote:
> With the following I got a syntax error:
> select specimen_id 
> from specimen_test_bits 
> where test_bit_id = all(1,2,3,4);

where test_bit_id in (1,2,3,4)
group by specimen_id
having count(distinct test_bit_id) = 4;

depesz

-- 
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Thanks :) it works as expected 

Julien

On Wed, 2007-11-14 at 14:31 +0100, hubert depesz lubaczewski wrote:
> On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote:
> > With the following I got a syntax error:
> > select specimen_id 
> > from specimen_test_bits 
> > where test_bit_id = all(1,2,3,4);
> 
> where test_bit_id in (1,2,3,4)
> group by specimen_id
> having count(distinct test_bit_id) = 4;
> 
> depesz
> 

Julien Cigar escreveu:
> Hello,
> 
> I have a problem with the ALL() subquery expression.
> I have three tables:
> - specimens
> - test_bits
> - specimen_test_bits
> 
> The specimen_test_bits table contains two foreign keys, one to
> specimens(id), another to test_bits(id). 
> 
> Here is an output of specimen_test_bits:
> 
> muridae=> select * from specimen_test_bits;
>  specimen_id | test_bit_id 
> -------------+-------------
>        46096 |           1
>        46096 |           2
>        46096 |           3
>        46096 |           4
>        52894 |           1
>        52894 |           3
>        12546 |           2
> 
> What I would like is a query that returns all the specimen_id of 
> this table which have _all_ the given test_bit_id. So in this
> case, with test_bit_id 1,2,3,4 it should return only 
> specimen_id 46096.
> 
> With the following I got a syntax error:
> select specimen_id 
> from specimen_test_bits 
> where test_bit_id = all(1,2,3,4);
> 
> The following works but no rows are returned :
> select specimen_id 
> from specimen_test_bits 
> where test_bit_id = all(select id from test_bits where id in (1,2,3,4));
> 
> Any idea how I could do this ? I guess the problem is my ALL() expression ...
> 


Unclear, but works...

SELECT DISTINCT stb.specimen_id FROM specimen_test_bits stb WHERE NOT EXISTS (SELECT * FROM (VALUES (1), (2), (3) , 
(4)) AS foo(id)                    WHERE NOT EXISTS (SELECT 
stb1.test_bit_id FROM specimen_test_bits stb1                                       WHERE foo.id = 
stb1.test_bit_id                                         AND 
stb.specimen_id = stb1.specimen_id));

Osvaldo