Why not: SELECT * FROM table1, table2 WHERE table2.columnB = 'this' AND FIND_IN_SET(table2.columnA, REPLACE(table1.columnA,';',',') ) > 0
Documented here: http://www.mysql.com/doc/en/String_functions.html Ted Gifford -----Original Message----- From: Tobias Asplund [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 1:26 PM To: Chris Boget Cc: [EMAIL PROTECTED]; MySQL Subject: Re: Stumped on a query On Tue, 20 Jan 2004, 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. Oops INSTR(a, CONCAT(',', b, ',')) OR INSTR(a, CONCAT(b, ',')) = 1 OR INSTR(a, CONCAT(',', b)) = CHAR_LENGTH(a) - CHAR_LENGTH(b) might work better, sorry about that (replace a and b with appropriate columns). The first line checks if ,column, exists, which will be in all cases when it's not either first or last. The second line checks if it exists first in the commaseparated list, and the last line checks if it exists last. There's probably an easier way to do this, but since I submitted a faulty reply I should atleast make up for it ;) cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
