MySQL LIKE from JOIN clause and GROUP_CONCAT returns only one row from joined table

Question

I have 3 tables: regions, realestate and one-to-many realestate_regions table.
I need to search realestate by address. I have following select query:

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;

This gives me following result:

+----+---------------+
| id | address       |
+----+---------------+
|  1 | San Francisco |
+----+---------------+

But what I need is:

+----+------------------------+
| id | address                |
+----+------------------------+
|  1 | USA, CA, San Francisco |
+----+------------------------+

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.

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;

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.
How can I get full address and be able to filter results with LIKE condition?

Tables:

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;

Sample data:

Table1: realestate. Main table with most of the data. There is much more columns, but I left those out from example for sake of clarity.

+----+--------------+
| id | random_data  |
+----+--------------+
|  1 | object A     |
|  2 | object B     |
+----+--------------+

Table2: regions. This table consists of various address strings.

+----+---------------+
| id | name          |
+----+---------------+
|  1 | USA           |
|  2 | CA            |
|  3 | San Francisco |
|  4 | Los Angeles   |
+----+---------------+

Table3: realestate_regions. One-to-many table connecting address strings to object.

+-----+-----+
| rid | reid|
+-----+-----+
|   1 |   1 |
|   2 |   1 |
|   3 |   1 |
|   1 |   2 |
|   2 |   2 |
|   4 |   2 |
+-----+-----+

Answer 1

The problem is you need the where to occur after the group concat This is one way using a sub select

Select * from (
    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 
    GROUP BY re.id) b
    WHERE (Address LIKE '%san%') 

Another... and more standard would be to use the having which applies after the aggregate is calculated.

    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 
    GROUP BY re.id 
    Having address like '%san%' 

I still can't attest that the order of the group_concat will be consistent when multiple records are encountered.