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

---

--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?