0

I am trying to write a query to pull all the rows that contain a username from a large list of usernames in a field.

For example, the table contains a column called 'Worklog' which contains comments made by users and their username. I need to search that field for all user names that are contained in a list I have.

I have tried a few different things but can't get anything to work. So far, this is kind of what I have tried:

SELECT * 
FROM `JULY2010` 
WHERE `WorkLog` 
IN (
     SELECT CONCAT( '%', `UserName` , '%' ) 
     FROM `OpsAnalyst`
)

The problem is I need to use LIKE because it is searching a large amount of text, but I also have a large list that it is pulling from, and that list needs to be dynamic because the people that work here are changing frequently. Any ideas?

Mike
  • 21,301
  • 2
  • 42
  • 65
mitchellwright
  • 171
  • 2
  • 11
  • Why do you need to use like? cant you just join the two tables? – Tim Aug 26 '10 at 15:33
  • look to this answer. meybe regexp help you http://stackoverflow.com/questions/1127088/mysql-like-in – Michael Pakhantsov Aug 26 '10 at 15:35
  • @Tim I don't have anything that I can actually join the tables on. No common columns. Perhaps I misunderstand your answer? I'm kind of new to SQL and everything I've tried so far has been from what I've found searching Google for people with similar problems. – mitchellwright Aug 26 '10 at 15:58

2 Answers2

0
 SELECT * 
 FROM `JULY2010` 
 WHERE `WorkLog`  REGEXP
  (SELECT CONCAT( `UserName`, '|') 
   FROM `OpsAnalyst`)
Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
0

I slightly modified this and used GROUP_CONCAT() and now my query looks like this: 

SELECT * 
FROM JULY2010 
WHERE `WorkLog` 
REGEXP (
    SELECT GROUP_CONCAT(`UserName` SEPARATOR '|') FROM `OpsAnalyst`
)

I am now getting a result set, but it seems like it isn't as many results as I should be getting. I'm going to have to look into it a little more to figure out what the problem is

mitchellwright
  • 171
  • 2
  • 11