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.w.noon@ntlworld.com (David W Noon)
=======================================================================