title: MySQL Join Table on Random Like
body: <p>I'm trying to join a table using a like and returning a random result.</p>
  
  <p>Essentially I'm just to join on a domain to subdomain format...</p>
  
  <p>eg:
  Domain: indonesian.dating
  Subdomain (Potential Matches): asia.indonesian.dating or bandung.indonesian.dating etc.</p>
  
  <p><a href="https://i.stack.imgur.com/8QnLw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8QnLw.png" alt="enter image description here"></a></p>
  
  <p>One Query I've been trying is:</p>
  
  <pre><code>SELECT b.domainname subdomain, a.*
  FROM facebook AS a
  JOIN domains AS b ON a.domainname LIKE '%testing.dating' ORDER BY RAND() LIMIT 1
  WHERE 
  a.datetime &lt; NOW();
  </code></pre>
  
  <p>Note: the facebook table has 130 domains and the return result needs to have one random subdomain for each of the 130 facebook domains. eg:</p>
  
  <p>indonesian.dating -> bule.indonesian.dating
  africa.dating -> lagos.africa.dating
  japanese.dating -> tokyo.africa.dating.</p>
  
  <p>and if the query was run again the subdomain would change as they are Random...</p>
  
  <p>Is this possible? I can do a separate query for the subdomains but would much rather use one domain...</p>
  
  <p>thanks
  adam</p>
  
  <p>Current Query - Group By not working yet...
  Note: handle is a unique value like the domain names in facebook table...</p>
  
  <pre><code>SELECT * FROM (
  SELECT b.domainname subdomain, a.*, a.handle
  FROM facebook AS a
  join domains b on b.domainname like concat("%",a.domainname)
  WHERE 
  a.datetime &lt; NOW()
  ORDER BY RAND()
  ) AS FBPages
  GROUP BY handle
  </code></pre>