MySql : Group rows my multiple columns and return the count

Question

I have a MySql table having the following structure:

ontology_term
pathway_id
pathway_name

I want to write a query using which we can get mapping between various pathways (having unique id's -> pathway_id) based on the number of common ontology terms.

So the output should be,

Pathway_id1, Pathway_id2, No. of common terms

I know, it can be easily done using a server side language, will it be faster to use MySql instead?

Answer 1

If I understood you right, that is

select a.pathway_id, b.pathway_id, count(*)
from t a
inner join t b on a.ontology_term = b.ontology_term
group by a.pathway_id, b.pathway_id

There is no record if two pathways do not have common terms