X or Y group?

Question

I am trying to figure out how GROUP BY on multiple columns. I want to group items when the SSN or address matches. For example, here are three entries:

 account_number | name | ssn | address ---------------+--------------+-------------+---------------------- 23952352340 | SMITH INC | 123-45-6789 | PO BOX 123 3459450340 | JOHN SMITH | 123-45-6789 | 123 EVERGREEN TERRACE 45949459494 | JANE SMITH | 395-23-1924 | 123 EVERGREEN TERRACE 

And here is what I would like to conclude:

 names ---------------------- SMITH INC, JOHN SMITH, JANE SMITH 

Any suggestions?

Answer 1

You cannot do this easily in MySQL .

The problem is that the “like” relationship, as you define it, is not transitive. In your example, Smith Inc is similar to John Smith (for SSN ), and John Smith is similar to Jane Smith (in name), but Smith Inc not similar to Jane Smith . Thus, there is no single value with which all records can be compared, and GROUP BY will not help here.

On other systems that support recursion, you can construct a transitive closure of this relationship that would allow grouping, but this is not an easy task in MySQL .

Answer 2

Like this:

 SELECT name, ssn, COUNT(*) FROM TheTable GROUP BY name, ssn