0
SELECT 
    rec.CONSTITUENT_ID as 'RE Const ID', 
    CASE 
       WHEN soft.giftID IS NULL AND con.first_name IS NULL 
          THEN con.Key_Name
       WHEN soft.giftID IS NULL AND con.first_name IS NOT NULL 
          THEN con.first_name + ' ' + con.KEY_NAME
       WHEN soft.giftID IS NOT NULL AND con3.first_name IS NULL 
          THEN con3.Key_Name
       WHEN soft.giftID IS NOT NULL AND con3.first_name IS NOT NULL 
          THEN con3.first_name + ' ' + con3.KEY_NAME
    END as 'Constituent Name',
    gift.usergiftid AS 'RE Gift ID', 
    CONVERT(varchar(12),gift.dte, 10) AS 'Gift Date', 
    (CASE 
        WHEN soft.amount IS NULL 
           THEN gift.amount 
        WHEN soft.amount IS NOT NULL 
           THEN soft.amount 
     END) AS 'Total Gift Amount', 
     CASE 
        WHEN soft.ConstitID IS NOT NULL 
           THEN 'Yes' 
           ELSE 'No' 
     END AS 'Soft Credited?'
FROM 
    dbo.gift gift
LEFT OUTER JOIN 
    dbo.GiftSoftCredit soft ON soft.giftID = gift.id and soft.giftID IS NOT NULL
LEFT OUTER JOIN 
    dbo.records rec ON (CASE WHEN soft.giftID IS NOT NULL THEN soft.ConstitID WHEN soft.giftID IS NULL THEN gift.constit_ID END) = rec.ID
LEFT OUTER JOIN 
    dbo.constituent con ON con.records_ID = rec.ID 
                        AND con.spousename = '0' 
                        AND con.sequence = '0' 
                        AND soft.GiftID IS NULL 
LEFT OUTER JOIN 
    dbo.constituent con3 ON con3.RECORDS_ID = rec.id 
                         AND soft.GiftID IS NOT NULL 
                         AND con3.spousename = 0 
                         AND con3.sequence = 0
LEFT OUTER JOIN 
    dbo.GiftSolicitor sol ON sol.parentID = gift.id AND sol.sequence = 1
LEFT OUTER JOIN 
    dbo.constituent con2 ON con2.RECORDS_ID = sol.SolicitorId 
                         AND con2.sequence = 0 
                         AND con2.spousename = 0
WHERE 
    gift.dte BETWEEN '2016-10-01' AND '2016-10-08'
    AND gift.amount > 0.0000 
    AND con2.records_ID IS NULL
    AND gift.type IN ('1','2','3','31')

Second portion that causes this not to work

    AND rec.constituent_ID NOT IN 
        (SELECT distinct rec.constituent_ID 
         FROM dbo.gift gift
         LEFT OUTER JOIN dbo.GiftSoftCredit soft ON soft.giftID = gift.id
         LEFT OUTER JOIN dbo.records rec ON (CASE WHEN soft.giftID IS NOT NULL THEN soft.ConstitID WHEN soft.giftID IS NULL THEN gift.constit_ID END) = rec.ID
         WHERE gift.dte BETWEEN '1991-01-01' AND '2016-09-30')

The goal is that I will get records from the top portion of the query that are not in the subquery at the bottom.

When I run the top portion of the sql on its own, I get 478 records. when I run the select in the bottom portion of the code I get 100970 records. When I compare these lists in excel with a vlookup, there are 173 constituent_id records that are not in that sub-query. But when I run the query all together I get NO records.

I have no idea what I am doing wrong here and I am still learning to a large degree with some of this, but I have no one to go to for help because I am all self-taught. I'm hoping I can get some help here.

I am on SQL Server 2008 R2 (SP2).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jana
  • 1
  • Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange Mar 13 '17 at 18:17
  • Could you have a problem with NULLs? [NOT IN clause and NULL values](http://stackoverflow.com/questions/129077/not-in-clause-and-null-values). The Left Join could return them... – TDP Mar 13 '17 at 19:54
  • Thanks TDP. I believe this is the issue. I really appreciate your help. – Jana Mar 13 '17 at 20:32

1 Answers1

0

From this link: NOT IN clause and NULL values

select 'true' where 3 not in (1, 2, null) returns nothing, and your SQL could return NULLs for the rec.constituent_ID column in your final section.

Would the logic still be valid if you change the LEFT OUTER JOINS to INNER JOINS? INNER JOINS would remove the NULLs.

Community
  • 1
  • 1
TDP
  • 1,141
  • 1
  • 9
  • 24