Rory Campbell-Lange wrote:
> I have been informed that at present (postgres 7.3.2) using IN is not
> advised, and I should replace it with EXISTS. I can't seem to get it to
> work.
...
>
> SELECT
> name
> FROM
> people
> WHERE
> exists (
> SELECT
> 1
> FROM
> states
> WHERE
> name ~* 'r'
> );
You should correlate the subquery with the outer query:
SELECT name
FROM people
WHERE EXISTS (
SELECT 1
FROM states
WHERE people.state = states.id AND
states.name ~* 'r'
);
But I don't see why you just don't use a join:
SELECT people.name
FROM people, states
WHERE people.state = states.id AND
states.name ~* 'r';
Hope that helps,
Mike Mascari
[EMAIL PROTECTED]
>
> However the second example simply finds all records in people.
>
> Thanks for any help,
> Rory
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
