IN & ANY subqueries  
Index ‹ mysql
Author Message
petervandijck
PostPosted: 2006-9-12 21:23:30 Top
mysql, IN & ANY subqueries Or perhaps add a TEXT index to the table?

Peter

On 9/6/06, Philippe Poelvoorde <email***@***.com> wrote:
> 2006/9/5, Ben Lachman <email***@***.com>:
> > So possibly a better solution would be to create a temporary table
> > and then do a subquery on that table?
>
> yes. Or do a :
> SELECT id FROM t1 WHERE name LIKE '%s1%' OR name LIKE %'s2%' OR name
> LIKE '%s3%'
> but depending on your data, a table may be the way to go.
>
> >
> > ->Ben
> >
> > On Sep 4, 2006, at 7:37 AM, Visolve DB Team wrote:
> >
> > > Hi
> > >
> > > The ANY keyword, which must follow a comparison operator, means
> > > "return TRUE if the comparison is TRUE for ANY of the values in the
> > > column that the subquery returns
> > >
> > > In has 2 forms:
> > > 1. IN (subquery). [The word IN is an alias for = ANY (subquery)].
> > > 2. IN (list of values seperated by comma)
> > >
> > > Hence the exact syntax to use is:
> > > Select <fields> from < table> where <fieldname> = ANY ( select
> > > <fieldname> from <table>);
> > >
> > > Ref: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-
> > > subqueries.html
> > >
> > > Thanks
> > > ViSolve DB Team
> > >
> > > ----- Original Message -----
> > > From: "Ben Lachman" <email***@***.com>
> > > To: <email***@***.com>
> > > Sent: Sunday, September 03, 2006 10:33 AM
> > > Subject: IN & ANY subqueries
> > >
> > >
> > >> I have a string comparison that I would like to do against a short
> > >> list of constant strings.
> > >>
> > >> at the moment I am using the syntax
> > >>
> > >> SELECT id FROM t1 WHERE name IN('s1', 's2', 's3', ...);
> > >>
> > >> However, this limits me to exact matches and I'd like to move to a
> > >> caparison expersion that lets me match names that contain any of the
> > >> list. The MySQL docs state that 'IN()' is an alias to '= ANY()'
> > >> however when I substitute' = ANY' for IN I get a parse error. What
> > >> I'd like to do is write something like (although I figure there may
> > >> be a better way to do the comparison that I am not thinking of):
> > >>
> > >> SELECT id FROM t1 WHERE name LIKE ANY('%s1%', '%s2%', '%s3%', ...);
> > >>
> > >> Does anyone know a way to do this?
> > >>
> > >> Thanks,
> > >>
> > >> ->Ben
> > >>
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=email***@***.com
> >
> >
>
>
> --
> http://www.myspace.com/sakuradrop : forget the rest
> http://www.w-fenec.org/ Webzine rock/metal
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=email***@***.com
>
>


--
find videoblogs: http://mefeedia.com
my blog: http://poorbuthappy.com/ease/
my job: http://petervandijck.net
 
 
Index ‹ mysql