Compare if same data in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Compare if same data in a column

Author

Topic

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-10-08 : 05:57:32

Hi,

I have the following case.

Select distinct col1, col2
from table
where col1 like 'ABC'

The result gives me
ABC ZGA
ABC ZPZ

Select distinct col1, col2
from table
where col1 like 'DEF'

The result gives me
DEF XXX
DEF XXX

As you can see I would like to know which of the same col1 in the table have different col2. Is that possible, if so how is it written in sql?

If I write:
Select col1, col2
from table

I will get a result of more than 12000 rows, and it is a waste of time to look for all col1 one by one to find out which col1 example 'ABC' is occuring more than once and have different col2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 06:07:02

[code]SELECT col1,col2
FROM table t1
JOIN (SELECT col1
FROM table
GROUP BY col1
HAVING COUNT(col2)>1)t2
ON t1.Col1=t2.Col1[/code]

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-08 : 06:20:38

How can distinct col1, col2 return

DEF XXX
DEF XXX

do you have spaces in col2? Is col2 varchar(3)?

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-10-08 : 06:30:05

Hi visakh16,

thank you for your answer. However, that query will give me result for both select queries as i showed in my first message.

You wrote t1.Col1=t2.Col1, but I think that will not help me find which ones of col1 having col2 that are different.

The thing is it should be something like t1.col2 = t2.col2 which should not give me a result if col1 is 'ABC' and col2 are 'ZGA' and 'ZPZ'. So 'DEF' should give me a hit because col2 have 'XXX' in both rows. But in your select statement I get a result for both.

Do you think there is a way to fix this?

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-10-08 : 06:31:06

bjoerns

Ok, i did not write all columns from the table. I wrote two to make the query look easier.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 06:32:51

quote:
Originally posted by Kurmanc

Hi visakh16,

thank you for your answer. However, that query will give me result for both select queries as i showed in my first message.

You wrote t1.Col1=t2.Col1, but I think that will not help me find which ones of col1 having col2 that are different.

The thing is it should be something like t1.col2 = t2.col2 which should not give me a result if col1 is 'ABC' and col2 are 'ZGA' and 'ZPZ'. So 'DEF' should give me a hit because col2 have 'XXX' in both rows. But in your select statement I get a result for both.

Do you think there is a way to fix this?

SELECT col1,col2
FROM table t1
INNER JOIN (SELECT col1
      FROM table
      GROUP BY col1
      HAVING COUNT(col2)>1
      AND COUNT(DISTINCT col2)=1)t2
ON t1.Col1=t2.Col1

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-08 : 06:37:45

Since you select distinct col1, col2, other columns do not matter.
Since you select distinct and get 'XXX' twice, they must be different, e.g.
'XXX' and 'XXX '. And Visakh's queries should return the same result for ABC and DEF.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 06:43:33

quote:
Originally posted by bjoerns

Since you select distinct col1, col2, other columns do not matter.
Since you select distinct and get 'XXX' twice, they must be different, e.g.
'XXX' and 'XXX '. And Visakh's queries should return the same result for ABC and DEF.

didnt notice that part. ceratinly it looks like col1 values are different. so no point in grouping on it.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-10-08 : 06:57:16

bjoerns,

You are right, but as I said, I did not write alla columns just to shorten the query. Anyway, I should have written all columns so I would not confuse anybody. Sorry for that.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-10-08 : 07:07:00

visakh16,

Your query solved my problem, thank you very much.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-08 : 07:07:03

No need to be sorry since the other columns are irrelevant. What does this query return?

SELECT col1, '>' + col2 + '<'
FROM Table
WHERE col1 = 'DEF'

And what is the datatype of col2?

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-10-08 : 07:10:51

bjoerns,

2 rows. >XXX<

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 07:11:07

quote:
Originally posted by Kurmanc

visakh16,

Your query solved my problem, thank you very much.

You're welcome
glad that i could help you out

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-10-08 : 07:11:37

you probably forgot "distinct"...

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-10-08 : 07:12:11

my last message was to bjoerns..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources