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