locked
Problem Getting SQL Statement to get all my records RRS feed

  • Question

  • Hi

    I have written a query see below which kinda works out.

    However, I also need to add to this code so I can get all records...

    where stc.value = 'E - Single'
    CON.AccountIdName is not equal to the companies listed in the query 
    and has null in their CON.AccountIdName field.

    Any help is appreciated - I am really stuck on this one.

    SELECT 
    	CON.new_MembershipNumber,
    	CON.AccountIdName,
    	CON.FirstName,
    	CON.LastName,
    	STC.Value
    	
    FROM dbo.Contact CON
    
    LEFT OUTER JOIN StringMap STC
               ON           STC.attributename  = 'new_coveragetype' 
                AND           STC.attributevalue = CON.new_CoverageType
                AND           STC.objecttypecode = 2 
                AND           STC.langid = 1033
    
    WHERE  CON.StatusCode	  = 1
    
    	AND CON.AccountIdName != 'Company 1'
    	AND CON.AccountIdName != 'Company 2'
    	AND CON.AccountIdName != 'Company 3'
    	AND CON.AccountIdName != 'Company 4'
    	AND CON.AccountIdName != 'Company 5'
    	AND CON.AccountIdName != 'Company 6'
    
        AND    STC.Value      =  'E - Single'
      
        
    
    
    select 'the number of rows is:', @@rowcount

    Thursday, July 11, 2013 10:04 PM

All replies

  • I believe new_coveragetype is a picklist. Please use the FilteredView for Contact that includes the display label of the picklist fields so you would not have to join the string map table.

    Also you could use the IN operator (Con.AccountIdName != null AND Con.AccountIdName NOT IN ('Company1', 'Company2'))

    Always use filtered views when accessing CRM data tables as it implements the CRM security privileges.

    HTH

    Sam


    Dynamics CRM MVP | Inogic | http://inogic.blogspot.com| news at inogic dot com

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    • Proposed as answer by Sam - Inogic Friday, July 12, 2013 3:53 AM
    Friday, July 12, 2013 3:53 AM
  • Hi

    I added in the "IN" clause however this did not fix anything.
    I need to get the record that match the criteria plus where their Accountidname is null as well

    So I missing 11 record from the database that match that criteria.
    This is what I don't know how to do

    SELECT 
    	CON.AccountIdName,
    	CON.FirstName,
    	CON.LastName,
    	STC.Value
    	
    FROM dbo.Contact CON
    
    LEFT OUTER JOIN StringMap STC
         ON           STC.attributename   = 'new_coveragetype' 
         AND           STC.attributevalue = CON.new_CoverageType
         AND           STC.objecttypecode = 2 
         AND           STC.langid = 1033
    
    WHERE  CON.StatusCode				= 1
    
    	 AND    STC.Value               = 'ES - Couple'
       
    	 AND CON.AccountIdName NOT IN
    	 (
    	 'Capo Industries Ltd.',
    	 'Pioneer Energy',
    	 'Salerno Dairy Products',
    	 'Trackless Vehicles Limited',
    	 'YMCA of Greater Toronto',
    	 'YMCA of Hamilton/Burlington'
    	 )
    	 
    	 AND CON.AccountIdName IS NULL

    Friday, July 12, 2013 10:10 AM