user3701157 user3701157 - 7 months ago 16
SQL Question

MySQL one to many relationship, with shared values

I have a MySQL DB with 3 tables.

profile,
p_tags,
tags

DB:

profile
pid | name

ptags
ptid | pid | tagid

tags
tagid | tag


The DB looks something like this

profile
pid | name
1 Randy
2 Jeff
3 Mike

ptags
ptid | pid | tagid
1 1 1
2 1 2
3 2 1
4 2 3
5 3 1
6 3 2
7 3 3

tags
tagid | tag
1 science
2 filosophy
3 hydrology


What I need is to create a query that displays The names in the profile that have shared tags and the total shared tags

I.E.

Results
Name | Name2 | Count
Randy - Jeff - 1
Randy - Mike - 2
Jeff - Randy - 1
Jeff - Mike - 2
Mike - Randy - 2
Mike - Jeff - 2


And so on.

I dunno if I explained it correctly.

I read

https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

From a link from another post similar to this one, but just can't get my head around it :).

SQLFiddle: http://sqlfiddle.com/#!9/42bde6

Answer

in query use join:

SELECT p1.name Name1, p2.name Name2, Count(tagid)
FROM ptags AS t1
INNER JOIN ptags AS t2 ON t2.tagid = t1.tagid and t2.ptid <> t1.ptid 
INNER JOIN profile AS p1 ON p1.pid = t1.pid 
INNER JOIN profile AS p2 ON p2.pid = t2.pid 
GROUP BY p1.name, p2.name

and if you need only one row for pair of firstname then try this:

SELECT p1.firstname Name1, p2.firstname Name2, Count(t1.tagid)
FROM profile_hashtags AS t1
INNER JOIN profile_hashtags AS t2 
  ON t2.tagid = t1.tagid and t2.pHid > t1.pHid 
INNER JOIN `profile` AS p1 ON p1.pid = t1.pid 
INNER JOIN `profile` AS p2 ON p2.pid = t2.pid 
GROUP BY p1.firstname, p2.firstname;