Select help - return rows that have a different column value for a particular family of records

Transact-SQL
1

I would like to return rows that have a different column value for a particular family of records. For instance, if I have a table consisting of State, Name, and Currency columns, and State is used to determine the family relationship. I only want to return values that have a different currency within the family (State).

Table name = Demo

Original Data

State	 Name	   Currency
NY       Paul  	   USD
NY       Tom	   CAD
NY       Nancy     USD
FLA      Jason     USD
FLA      Aimee     USD
CA       Beth	   USD
CA       Jeremy	   USD

The result set I am looking for is

State	Name	   Currency
NY      Paul	   USD
NY      Tom 	   CAD
NY      Nancy      USD

Hope this makes sense
Thanks!

2

Try this:

select a.state
      ,a.[name]
      ,a.currency
  from demo as a
 where exists (select 1
                 from demo as b
                where b.state=a.state
                  and b.currency<>a.currency
              )
;
3

Perfect, thanks!

4

Another one

select distinct 
	t.State, 
	t.Name, 
	t.Currency 
from #Test  as t
inner join #Test  as t2
on t.State =t2.State  and t.Currency <>t2.Currency
5

:+1: