On Tue, Jan 20, 2004 at 03:02:45PM -0600, Chris Boget wrote:
> > > The data I'm working with looks like this:
> > > table1.columnA = '1;3;4;6;8;9;12;13;14;15';
> > > table2.columnA = '3';
> > > table2.columnB = 'this';
> > > I need to write a query that will do something along these lines:
> > > SELECT * FROM table1, table2 WHERE
> > > table2.columnB = 'this'
> > > AND
> > > table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );
> > AND
> > INSTR(table1.columnA, table2.columnA)
>
> Well, the only problem with going this route is that if table1.columnA 's value
> was, instead, '1,4,6,8,9,13,14,15', that row would still match (when it really
> shouldn't) because of the '13' within the string. The '3' from table2.columnA
> is part of the table1.columnA string.
>
Try this:
INSTR(CONCAT(';',table1.columnA,';'),CONCAT(';',table2.columnA,';'))
Note that MySQL can't make use of any index here, so it will check all
n x m combinations, with n and m the number of records in table1 and
table2. You really want to do this for small tables only.
Consider an additional table to hold each of your 1, 3, 4, 6, ...
values together with the unique id of a record in table1.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
