title: mysql - select values from tables LIKE values from another table
body: <p>I have MySQL database with two tables: users and items. They look like this:</p>
  
  <p>users:</p>
  
  <pre><code>id | user_name | desc
  --------------------------
  1  | john      | tall
  2  | dave      | fat
  3  | maria     | pretty
  </code></pre>
  
  <p>items:</p>
  
  <pre><code>id | item_name | color
  --------------------------
  1  | trousers  | red
  2  | shoes     | blue
  3  | shoes     | red
  </code></pre>
  
  <p>I want to select from database list of items that have specific id and name of it is LIKE a keyword. It's for use of simple search engine.
  I am making such query:</p>
  
  <pre><code>SELECT id, user_name, ( SELECT item_name FROM items WHERE id = u.id ) as desc FROM users u WHERE desc LIKE "%shoes%" AND color LIKE "%blue%"
  </code></pre>
  
  <p>As a result, I would expect one line containing id = 2, username = dave and itemname = shoes because it's the only one row fulfilling my query. Unfortunately, I get a message that there is no a 'desc' column. I know that there is not such a column in 'users' but how tell MySQL to grab it from subquery named desc?</p>
  
  <p>Additional question: is that possible to work with WHERE ... LIKE command and array style like IN (val1, val2, val3)? I mean instead of loooong queries:</p>
  
  <pre><code>SELECT name 
  FROM users 
  WHERE name 
  LIKE "%john%" OR name 
  LIKE "%steven%" OR name LIKE "bob%"
  </code></pre>
  
  <p>make shorter:</p>
  
  <pre><code>SELECT name FROM users WHERE name LIKE IN ( "%john%", "%steven%", "%bob%")
  </code></pre>
  
  <p>Thanks in advance.</p>