where clauses and selects
Hello,
Is it possible to perform a select in the where clause of a statement?
I have a situation where I've got one arm tied behind my back: I can
only have a single table in the select and from clauses, but the where
clause appears to be freed from that restriction.
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
Scott Frankel <leknarf@pacbell.net> wrote:
Is it possible to perform a select in the where clause of a statement?
I have a situation where I've got one arm tied behind my
back: I can only have a single table in the select and from
clauses, but the where clause appears to be freed from that
restriction.
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.
[...]
Something along the lines of:
| SELECT foo.foo_id, foo.name FROM foo WHERE foo.bar_id = (SELECT bar.bar_id FROM bar WHERE bar.name = 'martini');
should do the trick.
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 ...
Dump it. Seriously. There are so many fine things you can do
with a full-fledged database - you certainly do not want to
be restricted in this regard.
Tim