title: MySQL Query to Match Unrelated Terms
body: <p>I'm trying to construct a query that's driving me crazy. I had no idea where to start with solving it, but after searching around a bit I started playing with subqueries. Now I'm at the point where I'm not sure if that will solve my issue or, if it will, how to create one that does what I want.</p>
  
  <p>Here's a very simplistic view of my current table (call it tbl_1):</p>
  
  <pre><code>---------------------------------
  |  row |  name  |  other_names  |
  |-------------------------------|
  |   1  |   A    |    B, C       |
  |   2  |   B    |    C          |
  |   3  |   A    |    C          |
  |   4  |   D    |    E          |
  |   5  |   C    |    A, B       |
  ---------------------------------
  </code></pre>
  
  <p>Some of the items I'm working with have multiple names (brand names, names in other countries, code names, etc.), but ultimately all of those different names refer to the same item. I originally was running a search query along the lines of:</p>
  
  <pre><code>SELECT * FROM tbl_1
  WHERE name LIKE '%A%'
  OR other_names LIKE '%A%';
  </code></pre>
  
  <p>Which would return rows 1 and 3. However, I quickly realized that my query should also return row 2, as A = B = C. How would I go about doing something like that? I'm open to alternative suggestions outside of a fancy query, such as constructing another table that somehow combines all the names into one row, but I figure something like that would be error prone or inefficient.</p>
  
  <p>Additionally, I'm running MySQL 5.5.23 using InnoDB with other code written in PHP and Python.</p>
  
  <p>Thanks!</p>
  
  <p><strong>Update 5/26/12:</strong><br>
  I went back to my original thinking of using a subquery, but right when I thought I was getting somewhere I ran into a documented MySQL issue where the query is evaluated from the outside in and my subquery will be evaluated for every row and won't finish in a realistic amount of time. Here's what I was attempting to do:</p>
  
  <pre><code>SELECT * FROM tbl_1
  WHERE name = ANY
      (SELECT name FROM tbl_1 WHERE other_names LIKE '%A%' or name LIKE '%A%')
  OR other_names = ANY 
      (SELECT name FROM tbl_1 WHERE other_names LIKE '%A%' or name LIKE '%A%')
  </code></pre>
  
  <p>Which returns what I want using the example table, but the aforementioned MySQL issue/bug causes the subquery to be considered a dependent query rather than an independent one. As a result, I haven't been able to test the query on my real table (~250,000 rows) as it eventually times out.</p>
  
  <p>I've read that the main workaround for the issue is to use joins rather than subqueries, but I'm not sure how I would apply that to what I'm trying to do. The more I think about it, I might be better off running the subqueries independently using PHP/Python and using the resulting arrays to craft the main query that I want. However, I still think there is the potential to miss some results because the terms in the columns aren't nearly as nice as my example (some of the terms are multiple words, some have parenthesis, the other names aren't necessarily comma-separated, etc). </p>
  
  <p>Alternatively, I'm thinking about constructing a separate table that will build the necessary links, something like:</p>
  
  <pre><code>| 1 | A | B, C|
  | 2 | B | C, A|
  | 3 | C | A, B|
  </code></pre>
  
  <p>but I think that's a lot easier said than done considering the data I'm working with and the non-standardized format in which it exists.</p>
  
  <p>The route that I'm strongly considering at the point is to build a separate table with the links that are easily constructed (i.e. 1:1 ratio for name:other_names) so I don't have to deal with the formatting issues that exist in the other_names column. I may also eliminate/limit the use of LIKE and require users to know at least one exact name in order to simplify the results and probably increase the overall performance.</p>
  
  <p>In conclusion, I hate working with input data that I have no control over.</p>