Return rows which have the same values in two columns, but different values in another-CodePudding

I have a table that looks like this:

       id  |           name           |      address       | code 
----------- -------------------------- -------------------- ----------
       101 | joe smith                | 1 long road        | SC1
       102 | joe smith                | 6 long road        | SC1
       103 | amy hughes               | 5 hillside lane    | SC5
       104 | amy hughes               | 5 hillside lane    | SC5

I want to return the rows that are duplications based on name and code but have different address fields.

I had something like this originally (which looked for duplications across the name, address and code columns:

SELECT name, address, code, count(*)
FROM table_name
GROUP BY 1,2,3
HAVING count(*) >1;

Is there a way I can expand on the above to only return rows that have the same name and code but different address fields?

In my example data above, I would only want to return:

       id  |           name           |      address       | code 
----------- -------------------------- -------------------- ----------
       101 | joe smith                | 1 long road        | SC1
       102 | joe smith                | 6 long road        | SC1

CodePudding user response：

Remove address from the select list and GROUP BY and use count(DISTINCT):

SELECT name, code, count(DISTINCT address)
FROM table_name
GROUP BY name, code
HAVING count(DISTINCT address) > 1;