return list of similar tagged urls mysql-CodePudding

urls table

url	date
http://url1	a
http://url2	b
http://url3	c
http://url4	d

tags table

url	tags
http://url1	x
http://url1	y
http://url2	x
http://url2	y
http://url3	x
http://url3	z
http://url4	z

the url table has url data and tags table has url tags data.

I want urls having similar tags ordered by the count of similar tags

eg1: if i want URLs which has similar tags as URL 'http://url1' the query should return

url	similar tags
http://url2	x,y
http://url3	x

'http://url1' has x,y

'http://url2' has x,y in common with 'http://url1'

'http://url3' has x in common with 'http://url1'

eg2: for 'http://url4'

url	similar tags
http://url3	z

'http://url3' has z in common with 'http://url4'

so urls should be returned order by similar tags count (desc)

note: The query should only return similar urls for specified url like in eg1 'http://url1'

i am not good at larger sql queries, so i need to ask this.

Thank you in advance

CodePudding user response：

SELECT
    urls.url,
    GROUP_CONCAT(t1.tags) as "similar tags"
FROM urls
INNER JOIN tags t1 ON t1.url=urls.url
INNER JOIN tags t2 ON t2.url='http://url1' and t2.tags = t1.tags
GROUP BY urls.url

Optionally you can add a WHERE urls.url <> 'http://url1' (Before the GROUP BY, and after the last INNER JOIN)

see: DBFIDDLE

EDIT: Starting from the table tags, like @Andrea_86 suggest, also works:

SELECT 
   t1.url, 
   GROUP_CONCAT(t1.tags) as "similar tags" 
FROM tags t1 
INNER JOIN tags t2 ON t2.url='http://url1' and t2.tags = t1.tags 
GROUP BY t1.url