pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SubSelect as a column
5+ messages / 4 participants
[nested] [flat]

* SubSelect as a column
@ 2000-02-08 00:54  Lars <lars@sscsinc.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Lars @ 2000-02-08 00:54 UTC (permalink / raw)
  To: pgsql-sql

I am trying to use a subselect as a column name, but it appears as if this
is not supported in Postgresql. Here is the query:

SELECT u.idnum, u.username,
  (SELECT COUNT(t.techid)
   FROM ticket t
   WHERE t.techid = u.idnum)
FROM users u;

the desired output would be:

idnum|username|?column?
-----+--------+--------
    6|lomboy  |       3
    2|stuart  |       6
    4|trevor  |       0
    9|victor  |       0

I can do this with the INNER JOIN:

SELECT u.idnum, MAX(u.username), COUNT(t.techid)
FROM users u, ticket t
WHERE t.techid = u.idnum
GROUP BY u.idnum;

But this will only return the those Whose count is not 0:

idnum|username|?column?
-----+--------+--------
    6|lomboy  |       3
    2|stuart  |       6

I have tried in vain to figure out how to do this correctly, but I am at a
loss. Any advice on how to get this to work.

Thank you very much in advance,

-Lars




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] SubSelect as a column
@ 2000-02-08 03:33  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Lars <lars@sscsinc.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Tom Lane @ 2000-02-08 03:33 UTC (permalink / raw)
  To: Lars <lars@sscsinc.com>; +Cc: pgsql-sql

Lars <lars@sscsinc.com> writes:
> I am trying to use a subselect as a column name, but it appears as if this
> is not supported in Postgresql. Here is the query:

> SELECT u.idnum, u.username,
>   (SELECT COUNT(t.techid)
>    FROM ticket t
>    WHERE t.techid = u.idnum)
> FROM users u;

Nope, doesn't work in 6.5.*.  It is there in current sources for the
upcoming 7.0 release.  Can't think of any good workaround in 6.5...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] SubSelect as a column
@ 2000-02-08 07:30  Nikolay Mijaylov <nmmm@nmmm.nu>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Nikolay Mijaylov @ 2000-02-08 07:30 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@postgreSQL.org>; Tom Lane <tgl@sss.pgh.pa.us>

Define this as function:

SELECT COUNT(t.techid)
FROM ticket t
WHERE t.techid = $1

Then use this....
SELECT u.idnum, u.username, func(u.idnum)
FROM users u;




--------------------------------------------------------------
The reboots are for hardware upgrades!
"http://www.nmmm.nu; <nmmm@nmmm.nu>


----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Lars <lars@sscsinc.com>
Cc: <pgsql-sql@postgreSQL.org>
Sent: вторник, Февруари 08, 2000 05:33
Subject: Re: [SQL] SubSelect as a column


> Lars <lars@sscsinc.com> writes:
> > I am trying to use a subselect as a column name, but it appears as if
this
> > is not supported in Postgresql. Here is the query:
>
> > SELECT u.idnum, u.username,
> >   (SELECT COUNT(t.techid)
> >    FROM ticket t
> >    WHERE t.techid = u.idnum)
> > FROM users u;
>
> Nope, doesn't work in 6.5.*.  It is there in current sources for the
> upcoming 7.0 release.  Can't think of any good workaround in 6.5...
>
> regards, tom lane
>
> ************




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] SubSelect as a column
@ 2000-02-08 09:19  Patrick JACQUOT <patrick.jacquot@anpe.fr>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 5+ messages in thread

From: Patrick JACQUOT @ 2000-02-08 09:19 UTC (permalink / raw)
  To: pgsql-sql

Tom Lane wrote:

> Lars <lars@sscsinc.com> writes:
> > I am trying to use a subselect as a column name, but it appears as if this
> > is not supported in Postgresql. Here is the query:
>
> > SELECT u.idnum, u.username,
> >   (SELECT COUNT(t.techid)
> >    FROM ticket t
> >    WHERE t.techid = u.idnum)
> > FROM users u;
>
> Nope, doesn't work in 6.5.*.  It is there in current sources for the
> upcoming 7.0 release.  Can't think of any good workaround in 6.5...
>
>                         regards, tom lane
>
> ************

i would do an UNION  of the tuples coming from the joint
with the tuples from "users" who don't have a counterpart in "ticket",
using "not exists" and adding to them a zero count.
Hoping it will help
regards
P.Jacquot




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] SubSelect as a column
@ 2000-02-08 19:08  Lars <lars@sscsinc.com>
  parent: Patrick JACQUOT <patrick.jacquot@anpe.fr>
  0 siblings, 0 replies; 5+ messages in thread

From: Lars @ 2000-02-08 19:08 UTC (permalink / raw)
  To: Patrick JACQUOT <patrick.jacquot@anpe.fr>; +Cc: pgsql-sql

Thanks, I will give that a try. Hopefully the NOT EXISTS will not be too
slow. It is my understanding, I could be very wrong, that NOT EXISTS will
not take advantage of my indexes.

On another note, any idea when PostgreSQL 7 will be released? It sure
would be nice to have the subselect as a colunm feature.

Thanks,

-Lars

On Tue, 8 Feb 2000, Patrick JACQUOT wrote:

> Tom Lane wrote:
> 
> > Lars <lars@sscsinc.com> writes:
> > > I am trying to use a subselect as a column name, but it appears as if this
> > > is not supported in Postgresql. Here is the query:
> >
> > > SELECT u.idnum, u.username,
> > >   (SELECT COUNT(t.techid)
> > >    FROM ticket t
> > >    WHERE t.techid = u.idnum)
> > > FROM users u;
> >
> > Nope, doesn't work in 6.5.*.  It is there in current sources for the
> > upcoming 7.0 release.  Can't think of any good workaround in 6.5...
> >
> >                         regards, tom lane
> >
> > ************
> 
> i would do an UNION  of the tuples coming from the joint
> with the tuples from "users" who don't have a counterpart in "ticket",
> using "not exists" and adding to them a zero count.
> Hoping it will help
> regards
> P.Jacquot
> 
> 
> ************
> 
> 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2000-02-08 19:08 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-08 00:54 SubSelect as a column Lars <lars@sscsinc.com>
2000-02-08 03:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-08 07:30   ` Nikolay Mijaylov <nmmm@nmmm.nu>
2000-02-08 09:19   ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
2000-02-08 19:08     ` Lars <lars@sscsinc.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox