Table tags:
article_id tag
1 Language
1 Java
1 c++
2 Language
2 c++
3 c++
and how can I write SQL(s) query(s) to make the data like below:
Table tags_relations:
tag1 tag2 relations_degree
Language C++ 2
Language Java 1
note: if the tag refers to the same article then the relations_degree + 1
The purpose of this approach is to calculate the relations between tags, can any one help me on this?
Something like:
SELECT A.tag AS tag1, B.tag AS tag2, COUNT(*) as relations_degree
FROM tags A
INNER JOIN tags B ON A.article_id = B.article_id
WHERE A.tag = 'Language' AND a.tag <> b.tag
GROUP BY A.tag, B.tag
ORDER BY B.tag ASC
Based on your example, I assumed you were limiting it to tag1 = 'Language', otherwise there's the case of tag1 = Java, tag2 = c++, relations_degree = 1. To get that:
SELECT A.tag AS tag1, B.tag AS tag2, COUNT(*) as relations_degree
FROM tags A
INNER JOIN tags B ON A.article_id = B.article_id
WHERE A.tag > b.tag
GROUP BY A.tag, B.tag
ORDER BY B.tag ASC