CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 20882

MySQL Join Table on Random Like

I'm trying to join a table using a like and returning a random result.

Essentially I'm just to join on a domain to subdomain format...

eg: Domain: indonesian.dating Subdomain (Potential Matches): asia.indonesian.dating or bandung.indonesian.dating etc.

enter image description here

One Query I've been trying is:

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 < NOW();

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:

indonesian.dating -> bule.indonesian.dating africa.dating -> lagos.africa.dating japanese.dating -> tokyo.africa.dating.

and if the query was run again the subdomain would change as they are Random...

Is this possible? I can do a separate query for the subdomains but would much rather use one domain...

thanks adam

Current Query - Group By not working yet... Note: handle is a unique value like the domain names in facebook table...

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 < NOW()
ORDER BY RAND()
) AS FBPages
GROUP BY handle

Upvotes: 0

Views: 46

Answers (1)

senape
senape

Reputation: 342

Try reordering the query:

SELECT b.domainname subdomain, a.*
FROM facebook AS a
JOIN domains AS b ON a.domainname LIKE concat('%', b.domainname)
WHERE 
a.datetime < NOW()
ORDER BY RAND() LIMIT 1;

Upvotes: 1

Related Questions