You should use something like: SELECT name FROM people p WHERE exists ( SELECT 1 FROM states WHERE name = p.state ) AND state ~* 'r';
On Tue, 2003-07-22 at 18:28, 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. > > I've tried replacing (example): > > SELECT > name > FROM > people > WHERE > state IN ( > SELECT > id > FROM > states > WHERE > name ~* 'r' > ); > > with > > SELECT > name > FROM > people > WHERE > exists ( > SELECT > 1 > FROM > states > WHERE > name ~* 'r' > ); > > However the second example simply finds all records in people. > > Thanks for any help, > Rory > > -- > Rory Campbell-Lange > <[EMAIL PROTECTED]> > <www.campbell-lange.net> > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match