Thread: Subquery problems

Subquery problems

From
Jerome Knobl
Date:
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


Re: [SQL] Subquery problems

From
Marc Howard Zuckman
Date:
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!                      _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_


Re: [SQL] Subquery problems

From
Dimitri
Date:
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
=====================================================