Check for duplicates within the select section

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)

Check for duplicates within the select section

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-10-29 : 11:42:24

Is it possible to test for a duplicate field within the select section of the following code? I want to return a 1 if they compared fields equal each other, a 0 if the do not.

Something like the following......:

SELECT productioned.dbo.company.mcic ORBIT_MCIC, DMCMAIN_IMPORT.idnumber IMPORTED_MCICS,
productioned.dbo.company.mcic = DMCMAIN_IMPORT.idnumber dup_MCIC,
productioned.dbo.company.company_name ORBIT_COMPANY_NAME, DMCMAIN_IMPORT.company IMPORTED_COMPANY_NAME
FROM productioned.dbo.company
INNER JOIN DMCMAIN_IMPORT
ON productioned.dbo.company.mcic = DMCMAIN_IMPORT.idnumber

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-29 : 11:52:00

you mean 2 columns having same value or 2 similar rows?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-10-29 : 11:57:53

I mean two different columns having the same value. I want to create a marker that will show that they match. I think this will work below:

SELECT productioned.dbo.company.mcic ORBIT_MCIC, DMCMAIN_IMPORT.idnumber IMPORTED_MCICS,
productioned.dbo.company.company_name ORBIT_COMPANY_NAME, DMCMAIN_IMPORT.company IMPORTED_COMPANY_NAME,
case when productioned.dbo.company.mcic = DMCMAIN_IMPORT.idnumber then '1' else '0' end as status
FROM productioned.dbo.company
INNER JOIN DMCMAIN_IMPORT
ON productioned.dbo.company.mcic = DMCMAIN_IMPORT.idnumber

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-29 : 12:01:32

Doesnt the SQL you have work? if not post the error messg.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-29 : 14:50:39

quote:
Originally posted by qman

I mean two different columns having the same value. I want to create a marker that will show that they match. I think this will work below:

SELECT productioned.dbo.company.mcic ORBIT_MCIC, DMCMAIN_IMPORT.idnumber IMPORTED_MCICS,
productioned.dbo.company.company_name ORBIT_COMPANY_NAME, DMCMAIN_IMPORT.company IMPORTED_COMPANY_NAME,
case when productioned.dbo.company.mcic = DMCMAIN_IMPORT.idnumber then '1' else '0' end as status
FROM productioned.dbo.company
INNER JOIN DMCMAIN_IMPORT
ON productioned.dbo.company.mcic = DMCMAIN_IMPORT.idnumber

Your SQL makes no sense, I'm afraid. You're going to always get a status of '1' because of the inner join (you're forcing the two columns to always match, so the case will always choose the '1' option - see where I marked your SQL in bold). So you'll always only see the ones that match because of the join, and therefore your case statement can be removed.

if you post your DDL (schema), sample data, and the results you're looking for, I'm sure we can help you figure out how to make things work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources