Re: where clauses and multiple tables

On Tue, 8 Sep 2009 14:25:20 -0700, Scott Frankel wrote about [GENERAL]
where clauses and multiple tables:

>Is it possible to join tables in the where clause of a statement?
[snip]
>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';

Just use an IN predicate:

SELECT foo_id, name FROM foo
WHERE bar_id IN (SELECT bar_id FROM bar WHERE name = 'martini');

This is frequently called a semi-join.
--
Regards,

Dave [RLU #314465]
=======================================================================
david(dot)w(dot)noon(at)ntlworld(dot)com (David W Noon)
=======================================================================