sql same name but different value

Question

How can you retrieve all the lines that have the same name, but they have different values? For example, I have this table:

Name|Value|Description
Rob |Val1 |Text1
Alex|Val1 |Text2
Alan|Val2 |Text3
Alex|Val2 |Text4
Alex|Val2 |Text5  
Alan|Val2 |Text6

I want the query to return just the persons that appear more than once, but have different values. So the results would be:

Alex|Val1 |Text2 
Alex|Val2 |Text4 
Alex|Val2 |Text5

Edit: I added another row to the initial table. Some of the quesries will return Alan as part of the result too. I don't want that, since clearly it has the same value. How can this be achieved?

Answer 1

Try to use join and sybquery with having:

  select p.Name,p.Value,p.Description
    from persons P
    join ( select Name,Value
           from persons
            group by Name,Value
            having count(1)>1
          ) c on p.Name = c.Name
              and p.Value = c.Value
    order by p.Name;

or the same subquery in exists clause:

select p.Name,p.Value,p.Description
from persons P
where exists
     ( select Name,Value
       from persons c
        where p.Name = c.Name
        group by Name,Value
        having count(1)>1
      ) 
order by p.Name          

SQL Fiddle Demo

Solution for updated problem

select * 
from Persons c
where name in 
(
select  Name   from(
       select Name,Value 
       from persons        
        group by Name,Value) T

group by Name
  having count(1)>1
)  

SQL Fiddle Demo

Answer 2

Just for fun ... A cross join might work something like (i haven't tested it) :

select distinct 
    p1.*
from 

    Persons p1 
cross join 
    Persons p2 
where 
        p1.Name = p2.Name 
    and p1.Value <> p2.value
    and p1.Description <> p2.Description 

Now you can start a discussion on how efficient this would be ... (But I won't be part of it ...)