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
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(
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
--
qui
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,
Julien Cigar wrote:
On Wed, 2007-11-14 at 11:56 +, 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 specim
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 = 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 ro
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
On Wed, 2007-11-14 at 11:56 +, 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 s
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 her
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
10 matches
Mail list logo
