Обсуждение: Subquery problems

Hello,

I'm trying to build some subquery, and it don't works like I want.

I try the following query :

select distinct pseudo, (select count(ref) from questions where
questions.refpseudo=pseudos.refold) as xxx from pseudos;

That query work well on MSAccess, but I have the following error on
postgres :

ERROR:  parser: parse error at or near "select"

I must maybe using an other method. I'm want to optain in a simple query
to list some name of my table pseudos and to see the number of occurency
in the table questions.

Thank. Jérôme Knöbl, Switzerland

On Wed, 12 Aug 1998, Jerome Knobl wrote:

> Hello,
>
> I'm trying to build some subquery, and it don't works like I want.
>
> I try the following query :
>
> select distinct pseudo, (select count(ref) from questions where
> questions.refpseudo=pseudos.refold) as xxx from pseudos;
>
> That query work well on MSAccess, but I have the following error on
> postgres :
>
> ERROR:  parser: parse error at or near "select"
>
> I must maybe using an other method. I'm want to optain in a simple query
> to list some name of my table pseudos and to see the number of occurency
> in the table questions.
>
> Thank. J�r�me Kn�bl, Switzerland
>
>
Subqueries in the target clause are not yet implemented in v 6.3.2


Marc Zuckman
marc@fallon.classyad.com

_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
_     Visit The Home and Condo MarketPlace              _
_          http://www.ClassyAd.com                  _
_                                  _
_  FREE basic property listings/advertisements and searches.  _
_                                  _
_  Try our premium, yet inexpensive services for a real          _
_   selling or buying edge!                      _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

On Wed, 12 Aug 1998, you wrote:
>Hello,
>
>I'm trying to build some subquery, and it don't works like I want.
>
>I try the following query :
>
>select distinct pseudo, (select count(ref) from questions where
>questions.refpseudo=pseudos.refold) as xxx from pseudos;

You have to try:

select pseudo,  count(ref) as xxx
  from pseudos, questions
  where questions.refpseudo=pseudos.refold
  group by pseudo;

or

select pseudo,  count(*) as xxx
  from pseudos, questions
  where questions.refpseudo=pseudos.refold
  group by pseudo;


This'll be more correct on SQL point and will be more faster
to execute because you will avoid duplication in your output...



>
>That query work well on MSAccess, but I have the following error on
???  M$ SQL is not a good reference... :))

>postgres :
>
>ERROR:  parser: parse error at or near "select"
>
>I must maybe using an other method. I'm want to optain in a simple query
>to list some name of my table pseudos and to see the number of occurency
>in the table questions.
>
>Thank. J�r�me Kn�bl, Switzerland

Hope it helps,
(dim)


--
=====================================================
 Dimitri KRAVTCHUK  (dim)           Sun Microsystems
 Benchmark Engineer                 France
 dimitri@France.Sun.COM
=====================================================