Use plain SQL to match pairs of rows from a table?

self-join, sql

Solution

What is the degree? It appears to be the maximum article id connecting two things.

This query requires a self join (and an insert):

insert into tag_relations(tag1, tag2, degree)
    select a1.tag_name, a2.tag_name, max(article_id) as maxid
    from articles a1 join
         articles a2
         on a1.article_id = a2.article_id and
            a1.tag_name < a2.tag_name
    group by a1.tag_name, a2.tag_name

The self join finds all pairs of items with the same article_id. The "<" just makes sure that reversing the order does not create a new pair. For any pair, the maximum article_id is taken as the new degree.

Problem

NOTICE: this question has a ambiguous concept about 'degree'. and I have got my answer already. These days I am struggling with a question like below: you can't write a function using some programming language; use RAW SQL only. Given a table `articles` which contains 2 columns: `article_id` and `tag_name` ``` article_id | tag_name --------------------------- 1 C++ 1 java 1 python 2 ruby 2 js 3 ruby 4 java 4 python ``` and an empty table named 'tag_relations' having a structure like: ``` tag1 | tag2 | degree ----------------------------------- ``` NOW, the question comes: - Write a "RAW SQL" to write values to the table 'tag_relations' according to the first table's content. For the given data, the output should look like ( I am not sure what the degree is, and the question hasn't mentioned about that, so, both the answers from Gordon Linoff and Jonathan Leffler are correct to me. ) : ``` tag1 | tag2 | degree ----------------------------------- java C++ 2 java python 4 ruby js 2 ``` UPDATED ADDITIONAL info NOTE 1. Here the 'degree' was not described by the question. However I think it's a measure for the relationships between 2 tags. Since there are values for 'java' and 'C++': ``` article_id | tag_name --------------------------- 1 C++ 1 java ``` so the degree = 2 and for 'java' and 'python' ``` article_id | tag_name --------------------------- 1 java 1 python 4 java 4 python ``` so the degree = 4 NOTE 2: There's no record for degrees with: 'python' and 'C++', (I mean this question doesn't refer to the cases that `degree < 2`) (Note by editor: this comment is inconsistent with including C++ and Java in the output.) I have spent a day on this question, and reviewed my knowledge on SQL, but still don't know how to store variables or loop the records. Any idea will be appreciated!

Original source