Actually, even better: select name from people p, states s where p.state = s.name and p.state ~* 'r';
Cheers, Csaba. On Tue, 2003-07-22 at 18:36, Csaba Nagy wrote: > 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 > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
