title: MySQL LIKE from JOIN clause and GROUP_CONCAT returns only one row from joined table
body: <p>I have 3 tables: regions, realestate and one-to-many realestate_regions table.<br>
  I need to search realestate by address. I have following select query:</p>
  
  <pre><code>SELECT re.id, GROUP_CONCAT( r.name ) AS address
  FROM realestate re 
  JOIN realestate_regions rr ON re.id = rr.reid 
  LEFT JOIN regions r ON rr.rid = r.id 
  WHERE ( re.id LIKE 'san%' OR r.name LIKE 'san%') 
  GROUP BY re.id;
  </code></pre>
  
  <p>This gives me following result:</p>
  
  <pre><code>+----+---------------+
  | id | address       |
  +----+---------------+
  |  1 | San Francisco |
  +----+---------------+
  </code></pre>
  
  <p>But what I need is: </p>
  
  <pre><code>+----+------------------------+
  | id | address                |
  +----+------------------------+
  |  1 | USA, CA, San Francisco |
  +----+------------------------+
  </code></pre>
  
  <p>Query returns only matching row from regions table, not all, which is logical, because of the LIKE condition. So I included separate JOIN for like condition.</p>
  
  <pre><code>SELECT re.id, GROUP_CONCAT( r.name ) AS address
  FROM realestate re 
  JOIN realestate_regions rr ON re.id = rr.reid 
  LEFT JOIN regions r ON rr.rid = r.id 
  LEFT JOIN regions r2 ON rr.rid = r2.id 
  WHERE ( re.id LIKE 'san%' OR r2.name LIKE 'san%') 
  GROUP BY re.id;
  </code></pre>
  
  <p>Hoping this would keep the first JOIN and its GROUP_CONCAT with all rows and run the condition only on second JOIN, but no, I get exactly same result.<br>
  <strong>How can I get full address and be able to filter results with LIKE condition?</strong><br></p>
  
  <p><strong>Tables:</strong></p>
  
  <pre><code>CREATE TABLE `realestate` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `random_data` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
  CREATE TABLE `regions` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
  CREATE TABLE `realestate_regions` (
      `rid` int(11) unsigned NOT NULL,
      `reid` int(11) unsigned NOT NULL,
      PRIMARY KEY (`rid`,`oid`),
      CONSTRAINT `realestate_regions_ibfk_2` FOREIGN KEY (`reid`) REFERENCES `realestate` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `realestate_regions_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `regions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB;
  </code></pre>
  
  <p><strong>Sample data:</strong><br></p>
  
  <p><strong>Table1:</strong> realestate. Main table with most of the data. There is much more columns, but I left those out from example for sake of clarity.</p>
  
  <pre><code>+----+--------------+
  | id | random_data  |
  +----+--------------+
  |  1 | object A     |
  |  2 | object B     |
  +----+--------------+
  </code></pre>
  
  <p><strong>Table2:</strong> regions. This table consists of various address strings.</p>
  
  <pre><code>+----+---------------+
  | id | name          |
  +----+---------------+
  |  1 | USA           |
  |  2 | CA            |
  |  3 | San Francisco |
  |  4 | Los Angeles   |
  +----+---------------+
  </code></pre>
  
  <p><strong>Table3:</strong> realestate_regions. One-to-many table connecting address strings to object.</p>
  
  <pre><code>+-----+-----+
  | rid | reid|
  +-----+-----+
  |   1 |   1 |
  |   2 |   1 |
  |   3 |   1 |
  |   1 |   2 |
  |   2 |   2 |
  |   4 |   2 |
  +-----+-----+
  </code></pre>