Complex query with IN

A simple query using IN would be something like:
SELECT * FROM foo
WHERE fld1 IN (SELECT fld1 FROM bar)
No problem. But what if the search criteria are more complex, for example,
matching against multiple columns? What I'd like to do is something like:
SELECT * FROM foo
WHERE fld1, fld2 IN (SELECT fld1, fld2 FROM bar)
but that is not valid SQL. How would I write it? Basically, for each row
in bar, I want to use fld1 and fld2 to select 1 (or more) rows from foo.
Any thoughts and help are greatly appreciated
On Thu, 14 Oct 2004 07:43:38 -0400, Andy Walldorff wrote:

>A simple query using IN would be something like:
(snip)
> What I'd like to do is something like:
>SELECT * FROM foo
>WHERE fld1, fld2 IN (SELECT fld1, fld2 FROM bar)
>but that is not valid SQL.
Hi Andy,
You can use the following equivalent:
SELECT Col1, Col2, ..., ColN
FROM foo
WHERE EXISTS (SELECT *
FROM bar
WHERE bar.fld1 = foo.fld1
AND bar.fld2 = foo.fld2)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo, I'll give it a try
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:peusm0dnqvss43p32cne98gcekf3cjutnv@.4ax.com...
> On Thu, 14 Oct 2004 07:43:38 -0400, Andy Walldorff wrote:
> (snip)
> Hi Andy,
> You can use the following equivalent:
> SELECT Col1, Col2, ..., ColN
> FROM foo
> WHERE EXISTS (SELECT *
> FROM bar
> WHERE bar.fld1 = foo.fld1
> AND bar.fld2 = foo.fld2)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)