MySQL Join Table on Random Like

Question

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.

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

Is the join column a real join? I mean, is the join domains b on a.domainname=b.domainname clause meaningful? In this case, is it a valuable option to rewrite your query as SELECT b.domainname subdomain, a.* FROM facebook AS a JOIN domains AS b ON a.domainname = b.domainname WHERE a.datetime < NOW() and a.domainname LIKE '%testing.dating' ORDER BY RAND() LIMIT 1;? — senape
– senape, Commented Feb 20, 2018 at 8:54
LIKE '%testing.dating' - this value needs to change - there are 130 domains I want to match a random subdomain from. If it was just the one I would do as you said... thanks for trying :) — Adam
– Adam, Commented Feb 20, 2018 at 8:58
what about join domains b on b.domainname like concat("%",a.domainname)? — senape
– senape, Commented Feb 20, 2018 at 9:12
Hey thanks so much... its not perfect (as I get multiple rows) but it is join an returning subdomains so its a great step forward... I'll see what I can do around limiting the rows returned per domain. thanks again!! — Adam
– Adam, Commented Feb 20, 2018 at 9:52
why it does return multiple rows? didn't you add the "where ... order by rand() limit 1" at the end of your query? — senape
– senape, Commented Feb 20, 2018 at 9:55

senape · Accepted Answer · 2018-02-20 10:04:14Z

1

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;

answered Feb 20, 2018 at 10:04

senape

3422 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

senape Over a year ago

To work properly, the group by clause needs an aggregation function. Consider adding a sum(), count(), group_concat() or whatever you need to make the "group by" meaningful

Collectives™ on Stack Overflow

MySQL Join Table on Random Like

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related