title: MySQL using IN/FIND_IN_SET to read multiple rows in sub query
body: <p>I have two tables, locations and location groups</p>
  
  <pre><code>CREATE TABLE locations (
      location_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(63) UNIQUE NOT NULL
  );
  
  INSERT INTO locations (name)
    VALUES
  ('london'),
  ('bristol'),
  ('exeter');
  
  CREATE TABLE location_groups (
      location_group_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
      location_ids VARCHAR(255) NOT NULL,
      user_ids VARCHAR(255) NOT NULL,
      name VARCHAR(63) NOT NULL,
  );
  
  INSERT INTO location_groups (location_ids, user_ids, name)
    VALUES
  ('1', '1,2,4', 'south east'),
  ('2,3', '2', 'south west');
  </code></pre>
  
  <p>What I am trying to do is return all location_ids for all of the location_groups where the given user_id exists. I'm using CSV to store the location_ids and user_ids in the location_groups table. I know this isn't normalised, but this is how the database is and it's out of my control.</p>
  
  <p>My current query is: </p>
  
  <pre><code>SELECT location_id 
    FROM locations 
    WHERE FIND_IN_SET(location_id, 
        (SELECT location_ids 
           FROM location_groups 
           WHERE FIND_IN_SET(2,location_groups.user_ids)) )
  </code></pre>
  
  <p>Now this works fine if the user_id = 1 for example (as only 1 location_group row is returned), but if i search for user_id = 2, i get an error saying the sub query returns more than 1 row, which is expected as user 2 is in 2 location_groups. I understand why the error is being thrown, i'm trying to work out how to solve it.</p>
  
  <p>To clarify when searching for user_id 1 in location_groups.user_ids the location_id 1 should be returned. When searching for user_id 2 the location_ids 1,2,3 should be returned. </p>
  
  <p>I know this is a complicated query so if anything isn't clear just let me know. Any help would be appreciated! Thank you.</p>