#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Philadephia
    Posts
    16
    Rep Power
    0

    Multiple Duplicate Records from Joins


    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
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    5
    Rep Power
    0
    I think you need to clarify your goals for this query.

    - What do you mean by cycle? Is this a date field?
    - Can duplicate records exist in your base table?
    - Can you post an example of the 4 records you receive using the tags?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Philadephia
    Posts
    16
    Rep Power
    0

    Response to Questions about Duplicates


    [QUOTE=bholmstrom]Good afternoon,
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    178
    Rep Power
    28
    [QUOTE=bholmstrom]
    Originally Posted by bholmstrom
    Good afternoon,
    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.
    Most likely, your WHERE criteria are not tight enough.
    I would start from the beginning. Comment out the StringMap.AttributeValue stuff; just get the basic data from the joins on AccountID and ContactID. Do you still get dupes?
    Then there must be something wrong with those IDs. Perhaps you are missing an additional foreign key to primary key link. Look at the schema definition to make sure.
    Also, the UPPER ... LIKE thing could be suspicious - how many rows do you have that match %REAL%?
    Then, look at the StringMap. Are you providing enough criteria for the AttributeName metadata?
    Hope this helps.

IMN logo majestic logo threadwatch logo seochat tools logo