Multiple Duplicate Records from Joins

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 2008 Forums

Transact-SQL (2008)

Multiple Duplicate Records from Joins

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2014-01-08 : 15:21:43

Good afternoon,
I have a query that uses several tables/joins to pull data. The query works the problem is that I get 4 records for each cycle. I only want one.

If I put an where clause at the bottom like
'CustomerAddress.city IS NOT NULL' then I get 2 rows per record.

Here is the query maybe someone can straighten me out.

Thanks in advance.

USE My_DataBase
go

select
'CRM 4.0 Record' as Comment_,
Contact.CONTACTID as UserID_,
left(ltrim(rtrim(contact.FirstName))+' '+ltrim(rtrim(Contact.LastName)),100) as FullName,
Substring(Contact.EmailAddress1,0,charindex( '@', Contact.EmailAddress1,0)) as UserNameLC_,
IndustryName.Value as Act_Industry,
IndustrySegName.Value as Act_Subsegment,
CustomerAddress.city as Act_City

FROM [My_DataBase].[dbo].[ContactBase] as Contact
LEFT JOIN [My_DataBase].[dbo].[CustomerAddressBase] as ContactAddress ON ContactAddress.ParentID = Contact.ContactID
JOIN [My_DataBase].[dbo].[AccountBase] as Account ON Account.AccountID = Contact.AccountId
LEFT JOIN [My_DataBase].[dbo].[CustomerAddressBase] as CustomerAddress ON CustomerAddress.ParentID = Account.AccountID
JOIN [My_DataBase].[dbo].[AccountExtensionBase] as AccountExt on AccountExt.AccountId = Account.AccountId
JOIN [My_DataBase].[dbo].[ContactExtensionBase] as ContactExt on ContactExt.Contactid = Contact.Contactid

LEFT JOIN [My_DataBase].[dbo].[StringMap] as IndustryName on
Account.IndustryCode = IndustryName.AttributeValue and IndustryName.AttributeName='industrycode'

LEFT JOIN [My_DataBase].[dbo].[StringMap] as IndustrySegName on
AccountExt.cpdc_subsegment = IndustrySegName.AttributeValue and IndustrySegName.AttributeName='cpdc_subsegment'

WHERE Contact.EmailAddress1 IS NOT NULL
AND
UPPER(IndustryName.Value) like '%REAL%'
and
CustomerAddress.city IS NOT NULL

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-09 : 05:07:47

thats because the relationship of tables joined below are one to many. SO for understanding what you want as output you need to post some sample data from tables to illustrate their one to many relationship and then explain what you want as output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2014-01-09 : 15:02:16

I have a query that uses several tables/joins to pull data. The query works, the problem is that I duplicate contact records.

If I put an where clause at the bottom like
'CustomerAddress.city IS NOT NULL' then I get 2 rows per record.

I added another join and now I.m getting many more duplicates.

I dont know how to post a sample file but I have one.

Sorry for the confusion ...here are the goals
1. get one contact record from the contact database that meets the where clause
2. I didnt mean cycle...just running the query
3. Duplicates could exist but in the sample I am posting

Here is the expanded code

select top 100
Account.Name,
Contact.CONTACTID as UserID_,
left(ltrim(rtrim(contact.FirstName))+' '+ltrim(rtrim(Contact.LastName)),100) as FullName,
Substring(Contact.EmailAddress1,0,charindex( '@', Contact.EmailAddress1,0)) as UserNameLC_,
IndustryName.Value as Act_Industry,
IndustrySegName.Value as Act_Subsegment,
CustomerAddress.city as Act_City,
ContactStatus.Value as Status

FROM [ReznickGroup_MSCRM].[dbo].[ContactBase] as Contact
LEFT JOIN [ReznickGroup_MSCRM].[dbo].[CustomerAddressBase] as ContactAddress ON ContactAddress.ParentID = Contact.ContactID
JOIN [ReznickGroup_MSCRM].[dbo].[AccountBase] as Account ON Account.AccountID = Contact.AccountId
LEFT JOIN [ReznickGroup_MSCRM].[dbo].[CustomerAddressBase] as CustomerAddress ON CustomerAddress.ParentID = Account.AccountID
JOIN [ReznickGroup_MSCRM].[dbo].[AccountExtensionBase] as AccountExt on AccountExt.AccountId = Account.AccountId
--JOIN [ReznickGroup_MSCRM].[dbo].[ContactExtensionBase] as ContactExt on ContactExt.Contactid = Contact.Contactid

LEFT JOIN [ReznickGroup_MSCRM].[dbo].[StringMap] as IndustryName on
Account.IndustryCode = IndustryName.AttributeValue and IndustryName.AttributeName='industrycode'

LEFT JOIN [ReznickGroup_MSCRM].[dbo].[StringMap] as IndustrySegName on
AccountExt.cpdc_subsegment = IndustrySegName.AttributeValue and IndustrySegName.AttributeName='cpdc_subsegment'

LEFT JOIN [ReznickGroup_MSCRM].[dbo].[StringMap] as ContactStatus on
Contact.StatusCode = ContactStatus.AttributeValue and ContactStatus.AttributeName='statuscode'

WHERE Contact.EmailAddress1 IS NOT NULL
AND
UPPER(IndustryName.Value) like '%REAL%'
AND
CustomerAddress.City IS NOT NULL
AND
(Contact.DoNotBulkEMail <> 1 or Contact.DoNotSendMM <> 1)
and
ContactStatus.Value = 'Active'

Now I am getting many more duplicates.

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 06:36:57

I repeat. please provide us some sample data and then explain what output you want out of it. No use posting query without knowing tables and how they're related
see guidelines to post question here
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources