I have a table with two columns, let's say FirstName and LastName. I need to get another table, which for every pair of FirstName's from the first one contains a count of the common LastName's.
Is this even feasible to do in SQL?
There are much more unique LastName's than FirstName's, if this affects efficiency of the query.
A toy example, input:
FirstName, LastName John, Smith John, Doe Jane, Doe
FirstName1, FirstName2, CommonLastNames John, John, 2 John, Jane, 1 Jane, Jane, 1 Jane, John, 1
As this relation is reflexive and symmetrical, it's OK if the result is just one of the triangles (e.g, the one above the diagonal).