locked
Difficulty adding in another inner join on my sql statement RRS feed

  • Question

  • Hi

    I have created the following sql statement (notice the commented code)

    How do I incorporate the 2nd inner join in to the sql statement.

    Sorry, just a newbie

    SELECT 
    
    CON.new_VIPContactType,
    CON.new_MembershipNumber  AS 'Member ID',
    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_CoverageType   AS 'Plan Type 1',
    CON.new_VIPPlanType   AS 'Plan Type',
    CON.new_VIPProductName	  AS 'Product Name',
    CONVERT(varchar,CON.new_MemberSince,101) AS 'Effective Date',
    SMB.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    SMB.ObjectTypeCode			= 10007
    AND    SMB.LangId					= 1033
    
    /*
     JOIN dboStringMap SMB1
    ON     CON.new_CoverageType			= SMB1.AttributeValue
    WHERE  SMB1.AttributeName			= 'new_coveragetype'      
    AND    SMB1.ObjectTypeCode			= 2
    AND    SMB1.LangId				= 1033
    */
    
    AND    CON.StatusCode				= 1

    Monday, July 1, 2013 6:53 PM

Answers

  • Try this

    SELECT 
    
    CON.new_VIPContactType,
    CON.new_MembershipNumber  AS 'Member ID',
    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_CoverageType   AS 'Plan Type 1',
    CON.new_VIPPlanType   AS 'Plan Type',
    CON.new_VIPProductName	  AS 'Product Name',
    CONVERT(varchar,CON.new_MemberSince,101) AS 'Effective Date',
    (select value from stringmap
     where attributename = 'new_address1_province' and
           attributevalue = CON.new_address1_province and
           objecttypecode = 10007 and
           langid = 1033) as 'Location Value',
    (select value from stringmap
     where attributename = 'new_coveragetype' and
           attributevalue = CON.new_CoverageType and
           objecttypecode = 2 and
           langid = 1033) as 'Coverage Type'
        
    
    FROM dbo.Contact CON


    Blake Scarlavai - http://mscrmdev.blogspot.com/ - Sonoma Partners - http://www.sonomapartners.com/ - Follow @bscarlav
    CRM 2011 JavaScript Model Generator - CRM 2011 Appender for log4net

    • Marked as answer by katiemackie Monday, July 1, 2013 7:22 PM
    Monday, July 1, 2013 7:09 PM