Get records where one column has the same values and another column has different values-CodePudding

I would like to get the record where one column has the same value and the other column has different value. See table below:

Name Type
XX01 Table
XX01 Chair
XX02 Box
XX02 Box

My end results will show XX01 since the column Name is the same but the column Type is different. I tried the following sql query which returns nothing:

select Name,Type from table where Name != Name

CodePudding user response：

You can solve this using JOIN, like this:

SELECT t1.Name
FROM table t1
LEFT JOIN table t2 ON t1.Name = t2.Name AND t1.Type != t2.Type

CodePudding user response：

If you need all the rows for the name which have different type:

select name, type from table 
where name in (select name from table 
               group by name
              having count(distinct type)>1)

If you only need names with different type then:

select name from table 
group by name
having count(distinct type)>1