Finding Duplicate Data - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Finding Duplicate Data

Author

Topic

stharish
Starting Member

43 Posts

Posted - 2012-06-26 : 18:10:33

I have a table containing ContractName(varchar) and ContractID(int). I'm looking for help creating a query to identify duplicate contract names.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-26 : 18:24:26

SELECT * FROM myTable
WHERE ContractName IN(SELECT ContractName FROM myTable GROUP BY ContractName HAVING COUNT(*)>1)

stharish
Starting Member

43 Posts

Posted - 2012-06-26 : 18:39:22

I should have mentioned that duplicate ContractName is okay as long as ContractID is different. Thanks

stharish
Starting Member

43 Posts

Posted - 2012-06-26 : 19:39:26

I had this wrong.

I have a table ACCOUNTS with columns AccountName and AccountID
I have another table CONTRACTS with columns ContractName and ContractID

Each account may contain multiple contracts with the same name but different contract ID's. I need to find contracts with matching names but under different accounts.

Thanks

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-06-27 : 18:42:27

quote:
Originally posted by stharish

I had this wrong.

I have a table ACCOUNTS with columns AccountName and AccountID
I have another table CONTRACTS with columns ContractName and ContractID

Each account may contain multiple contracts with the same name but different contract ID's. I need to find contracts with matching names but under different accounts.

Thanks

How do you link a contract to an account?
Is there perhaps a third table linking contractid to accountid?

stharish
Starting Member

43 Posts

Posted - 2012-11-02 : 00:41:30

Yes, I have a table called AccountContracts that contains both an AccountID and ContractID that I can use to join the Accounts and Contracts tables

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-02 : 04:00:12

[code]

--I have a table ACCOUNTS with columns AccountName and AccountID
--I have another table CONTRACTS with columns ContractName and ContractID
--I have a table called AccountContracts that contains both an AccountID and ContractID

set nocount on
declare @Accounts table(AccountID int, AccountName varchar(30))
declare @Contracts table(ContractID int, ContractName varchar(30))
declare @AccountContracts table(AccountID int, ContractID int)

--Each account may contain multiple contracts with the same name but different contract ID's.

insert @Accounts
select 1, 'Account A' union all
select 2, 'Account B' union all
select 3, 'Account C'

insert @Contracts
select 1, 'Contract A' union all
select 2, 'Contract B' union all
select 3, 'Contract C' union all
select 4, 'Contract A'

insert @AccountContracts
select 1,1 union all
select 1,4 union all
select 2,1 union all
select 2,2 union all
select 3,3

--I need to find contracts with matching names but under different accounts.

select a.AccountID, a.AccountName, c.ContractID, c.ContractName
from @Accounts a
join @AccountContracts ac on ac.AccountID = a.AccountID
join @Contracts c on c.ContractID = ac.ContractID

--AccountID AccountName ContractID ContractName
------------- ------------------------------ ----------- ------------------------------
--1 Account A 1 Contract A
--2 Account B 1 Contract A
--2 Account B 2 Contract B
--3 Account C 3 Contract C
--1 Account A 4 Contract A

In this example (if it isn't wrong) - what is the wanted result?
[/code]

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-02 : 04:03:56

btw. post your questions like I did it for you above and you will get good answers...

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources