Select rows from same table which have different values in a field

Databases
1

Lets say I have a table with three columns:

[table=“width: 300, class: grid, align: left”]
[tr]
[td]id[/td]
[td]name[/td]
[td]field[/td]
[/tr]
[tr]
[td]1[/td]
[td]test123[/td]
[td]abc[/td]
[/tr]
[tr]
[td]2[/td]
[td]test456[/td]
[td]abc[/td]
[/tr]
[tr]
[td]3[/td]
[td]test123[/td]
[td]def[/td]
[/tr]
[tr]
[td]4[/td]
[td]test456[/td]
[td]abc[/td]
[/tr]
[tr]
[td]5[/td]
[td]test789[/td]
[td]abc[/td]
[/tr]
[/table]

I want to select all “names” from the table which appear more than once and in which the field is not the same. In the above example, I should get one result back - the name “test123”. How would I do that?

2 

select
name, field
from
tablename
group by
name, field

This will return a result set of unique rows between the two columns. Now lets query this data set to see if there are duplicates of name…


SELECT t1.name
FROM (select
name, field
from
table1
group by
name, field
)  AS t1
GROUP BY t1.name
HAVING Count(t1.name)>1
3 
SELECT name
  FROM daTable
GROUP
    BY name
HAVING COUNT(DISTINCT field) > 1
4

Awesome. Thank you both. I was originally trying to write something like what K. Wolfe posted, but it looks like r937’s is cleaner. I always forget about using DISTINCT within the function like that.

One further question - is there a better way to select all “names” and “fields” from this result set, other than doing this:

SELECT t2.name, t2.field
  FROM (
      SELECT name
        FROM daTable
      GROUP
          BY name
      HAVING COUNT(DISTINCT field) > 1
) AS t1
LEFT JOIN daTable AS t2 ON t1.name = t2.name
5

yes – INNER JOIN :slight_smile: