Thread: 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 =====================================================