Help With the Query

[adimulam]

Hi,
My problem looks simple. But I am strugling to build a query to get the result I wanted.

I have a two column table. I need the rows that have more than one combination of values.

For example...

Col1: Col2:
1 A
1 A
2 B
2 C
3 B
3 B
3 C
4 D
4 D

In the above table, I want the resultset as follows..

2 B
2 C
3 B
3 B
3 C

Since Col1 values 1 and 4 do not have more than one Col2 values, I want those rows to be exluded from the resultset.

Any help will be appreciated.

Adimulam

 

[amrita418]

The query below should do it.

create table #TestAdimulam(Col1 int, Col2 char(1))

insert into #TestAdimulam values (1,         'A')
insert into #TestAdimulam values (1 ,        'A')
insert into #TestAdimulam values (2,         'B')
insert into #TestAdimulam values (2 ,        'C')
insert into #TestAdimulam values (3,         'B')
insert into #TestAdimulam values (3,         'B')
insert into #TestAdimulam values (3,         'C')
insert into #TestAdimulam values (4,         'D')
insert into #TestAdimulam values (4,         'D')

select 		a.* 
from 		#TestAdimulam a
where 		exists (select 	'x' 
			from 	#TestAdimulam b
              		where 	a.col1 = b.col1
              			and a.col2 != b.col2)

Regards,
AA

 

[vongrunt]

Good thinking.

Kinda more conventional - and probably less efficient - way is to find Col1 values with 2 or more distinct Col2 values then use inner join:

select a.*
from #TestAdimulam a
inner join
(	select Col1
	from #TestAdimulam
	group by Col1
	having count(distinct col2) > 1
) b on A.Col1=B.Col1

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)