Re: 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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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