Обсуждение: where clause question

Hello,

Is it possible to perform selects in a where clause of a statement?

Given a statement as follows:

     SELECT foo.foo_id, foo.name
     FROM foo, bar
     WHERE foo.bar_id = bar.bar_id
     AND bar.name = 'martini';

I'm looking for a way to recast it so that the select and from clauses
refer to a single table and the join referencing the second table
occurs in the where clause.  For example, something like this:

     SELECT foo.foo_id, foo.name
     FROM foo
     WHERE (SELECT * FROM foo, bar WHERE ...)
     foo.bar_id = bar.bar_id
     AND bar.name = 'martini';

I've explored the "where exists" clause, but it's not supported by the
application toolkit I'm using.  AFAIK, I've only got access to where ...

Thanks in advance!
Scott

On 4 Sep 2009, at 15:47, Scott Frankel wrote:

>
> Hello,
>
> Is it possible to perform selects in a where clause of a statement?
>
> Given a statement as follows:
>
>    SELECT foo.foo_id, foo.name
>    FROM foo, bar
>    WHERE foo.bar_id = bar.bar_id
>    AND bar.name = 'martini';

> I've explored the "where exists" clause, but it's not supported by
> the application toolkit I'm using.  AFAIK, I've only got access to
> where ...


Have you tried a view?

Is that some in-house toolkit you're using? If not, could you tell
what it is so that people can chime in with ways to use that toolkit
to get it do what you want or at least know what toolkit to avoid?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4aa1375011861997820494!

On Fri, Sep 4, 2009 at 9:47 AM, Scott Frankel<leknarf@pacbell.net> wrote:
>
> Hello,
>
> Is it possible to perform selects in a where clause of a statement?
>
> Given a statement as follows:
>
>    SELECT foo.foo_id, foo.name
>    FROM foo, bar
>    WHERE foo.bar_id = bar.bar_id
>    AND bar.name = 'martini';
>
> I'm looking for a way to recast it so that the select and from clauses refer
> to a single table and the join referencing the second table occurs in the
> where clause.  For example, something like this:
>
>    SELECT foo.foo_id, foo.name
>    FROM foo
>    WHERE (SELECT * FROM foo, bar WHERE ...)
>    foo.bar_id = bar.bar_id
>    AND bar.name = 'martini';
>
> I've explored the "where exists" clause, but it's not supported by the
> application toolkit I'm using.  AFAIK, I've only got access to where ...

where clauses is basically a set of boolean expressions.  It's not
completely clear how to wrap that inside what you are trying to do.

you can do this:
WHERE something = (SELECT * FROM foo, bar WHERE ...)

or this:

WHERE (SELECT count(*) FROM foo, bar WHERE ...) > 0

for example.  however, I'd advise dumping the application framework as
a long term objective.  Another general tactic to try and express what
you are looking for in a view and query the view in a more regular
way.  This is likely your best bet.

merlin

On Fri, Sep 04, 2009 at 06:47:24AM -0700, Scott Frankel wrote:
>
> Hello,
>
> Is it possible to perform selects in a where clause of a statement?
>
> Given a statement as follows:
>
>     SELECT foo.foo_id, foo.name
>     FROM foo, bar
>     WHERE foo.bar_id = bar.bar_id
>     AND bar.name = 'martini';
>
> I'm looking for a way to recast it so that the select and from clauses
> refer to a single table and the join referencing the second table occurs
> in the where clause.  For example, something like this:
>
>     SELECT foo.foo_id, foo.name
>     FROM foo
>     WHERE (SELECT * FROM foo, bar WHERE ...)
>     foo.bar_id = bar.bar_id
>     AND bar.name = 'martini';
>
> I've explored the "where exists" clause, but it's not supported by the
> application toolkit I'm using.  AFAIK, I've only got access to where ...

Sounds like a great reason to modify, or if you can't modify, replace,
that application toolkit.  This won't be the last time it will get in
your way.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

you'll need to create an alias beforehand

SELECT foo.foo_id, foo.name
FROM foo, (SELECT * FROM foo, bar WHERE ...) bar
WHERE foo.bar_id = bar.bar_id
AND bar.name = 'martini';

Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Fri, 4 Sep 2009 10:21:24 -0700
> From: david@fetter.org
> To: leknarf@pacbell.net
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] where clause question
>
> On Fri, Sep 04, 2009 at 06:47:24AM -0700, Scott Frankel wrote:
> >
> > Hello,
> >
> > Is it possible to perform selects in a where clause of a statement?
> >
> > Given a statement as follows:
> >
> > SELECT foo.foo_id, foo.name
> > FROM foo, bar
> > WHERE foo.bar_id = bar.bar_id
> > AND bar.name = 'martini';
> >
> > I'm looking for a way to recast it so that the select and from clauses
> > refer to a single table and the join referencing the second table occurs
> > in the where clause. For example, something like this:
> >
> > SELECT foo.foo_id, foo.name
> > FROM foo
> > WHERE (SELECT * FROM foo, bar WHERE ...)
> > foo.bar_id = bar.bar_id
> > AND bar.name = 'martini';
> >
> > I've explored the "where exists" clause, but it's not supported by the
> > application toolkit I'm using. AFAIK, I've only got access to where ...
>
> Sounds like a great reason to modify, or if you can't modify, replace,
> that application toolkit. This won't be the last time it will get in
> your way.
>
> Cheers,
> David.
> --
> David Fetter <david@fetter.org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david.fetter@gmail.com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Windows Live: Keep your friends up to date with what you do online. Find out more.