locked
Have issue adding in an inner join RRS feed

  • Question

  • Hi

    I have created 2 queries - one with an inner join and one without an inner join
    I don't know what Ive done wrong, but when I add in the inner join, my result set is incorrect

    Query 1 - without inner join:
    
    SELECT 
    CON.new_MembershipNumber AS 'Member ID',
    CON.new_VIPContactType  AS 'Contact Type',
    CON.AccountIdName	AS 'Company',
    CON.FirstName		AS 'First Name',
    CON.MiddleName		AS 'Middle Name',
    CON.LastName		AS 'Last Name',
    CONVERT(varchar,CON.BirthDate,101) AS 'DOB',
    CON.GenderCode	AS 'Gender',
    CON.new_VIPUserType AS 'User Type',
    CON.EMailAddress1 AS 'E-mail',
    CON.FullName AS 'Member Name',
    
    CON.new_VIPPlanType AS 'Plan Type',
    CON.new_VIPProductName	AS 'Product Name',
    CONVERT(varchar,CON.new_VIPEffectiveDate,101) AS 'Effective Date',
    CON.new_address1_province AS 'Location'
    
    FROM dbo.Contact CON
    
    WHERE  CON.new_VIPContactType	    = 'VIP Member'
    AND    CON.StatusCode               = 1
    
    
    when I run this query I get these results:  (1882 row(s) affected)
    which is correct
    
    Query 2 - with inner join
    
    SELECT 
    CON.new_MembershipNumber AS 'Member ID',
    CON.new_VIPContactType  AS 'Contact Type',
    CON.AccountIdName AS 'Company',
    CON.FirstName	AS 'First Name',
    CON.MiddleName	AS 'Middle Name',
    CON.LastName	AS 'Last Name',
    CONVERT(varchar,CON.BirthDate,101) AS 'DOB',
    CON.GenderCode	AS 'Gender',
    CON.new_VIPUserType AS 'User Type',
    CON.EMailAddress1 AS 'E-mail',
    CON.FullName AS 'Member Name',
    
    CON.new_VIPPlanType AS 'Plan Type',
    CON.new_VIPProductName	 AS 'Product Name',
    CONVERT(varchar,CON.new_VIPEffectiveDate,101)  AS 'Effective Date',
    CON.new_address1_province AS 'Location'
    dbo.StringMap.Value   AS 'Location Value'
    
    FROM dbo.Contact CON
    
    INNER JOIN dbo.StringMap SMB
    ON    CON.new_address1_province       	= SMB.AttributeValue
    WHERE SMB.AttributeName		      	 = 'new_address1_province'
    
    
    AND   CON.new_VIPContactType		= 'Advica Member'
    AND   CON.StatusCode               	= 1
    
    when I run this query I get these results: (11292 row(s) affected)
    which is incorrect
    


    Thursday, June 27, 2013 2:22 PM

All replies

  • You need to additionally filter on the relevant entity in StringMap (e.g. SMB.ObjectTypeCode = 2 'contact) and the label language. The definition of the filtered views should give an example

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, June 27, 2013 4:05 PM
    Moderator