Re: where clause question

On Fri, Sep 4, 2009 at 9:47 AM, Scott Frankel<leknarf(at)pacbell(dot)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