Solved

SQL NOT EXISTS not working

Posted on 2014-09-09
5
158 Views
Last Modified: 2014-09-26
Hi,

I wish to insert some records into a table only if they do not exist in another.

However I don't think my 'NOT EXISTS' is working correctly in my code. I can see from the stats that 4 policies have gone on but when I run my select statement it is only finding 2 policies.

I have manually searched for the 'Policy References' in the table I wish to compare to and they don't exist so I don't know why my NOT EXISTS is not working, can someone shed some light for me please.

SELECT DISTINCT
        [c].[Name] AS 'Name' ,
        CASE WHEN [pbr].[Affinity] IN ( 'Autosaint' , 'Ladybird' )
             THEN dbo.[GetTitle]([Name])
             ELSE ''
        END AS 'Title' ,
        LEFT([c].[Forenames] ,
             ISNULL(NULLIF(CHARINDEX(' ' , REVERSE([c].[Forenames])) - 1 , -1) ,
                    LEN([c].[Forenames]))) AS 'First Name' ,
        CASE WHEN CHARINDEX(' ' , [c].[Forenames] , 1) > 1
             THEN SUBSTRING([c].[Forenames] ,
                            CHARINDEX(' ' , [c].[Forenames] , 1) + 1 , 20)
             ELSE ' '
        END AS 'Middle Names' ,
        RIGHT(c.Name ,
              ISNULL(NULLIF(CHARINDEX(' ' , REVERSE(c.Name)) - 1 , -1) ,
                     LEN(c.Name))) AS Surname ,
        [c].[Addr1] AS 'Address line 1' ,
        [c].[Addr2] AS 'Address line 2' ,
        [c].[Addr3] AS 'Address line 3' ,
        [c].[Addr4] AS 'Address line 4' ,
        [c].[Pcode] AS 'Postcode' ,
        pbr.[Affinity] AS 'Brand' ,
        pol.[Insco] AS 'Insurer' ,
        [regtbl].[Reg] AS 'Reg' ,
        [pol].[Polno] AS 'Insurer Ref' ,
        [pol].[B@] AS 'Fresh Branch' ,
        ISNULL([lepol].[PolRef@],'BRNZ001') AS 'Add On Polref' ,
        [pol].[PolRef@] AS 'Fresh Ref' ,
        [pol].[Rdat] AS 'Renewal Date' ,
        [pol].[Idat] AS 'Inception Date' ,
		CASE WHEN lepol.PolRef@ IS NULL THEN 'No' ELSE 'Yes' END AS 'LOS' ,
        'N' AS [Status]
FROM    [dbo].[ic_yyclient] AS c
        LEFT OUTER JOIN [dbo].[ic_brpolicy] AS pol ON [c].[B@] = [pol].[B@]
                                                      AND [c].[Ref@] = [pol].[Ref@]
        LEFT OUTER JOIN ( SELECT    *
                          FROM      [dbo].[ic_brpolicy] AS lepol
                          WHERE     [lepol].[Ptype] = 'LE' AND [lepol].[Term_date] >= dbo.[Today]()
                        ) AS lepol ON pol.[B@] = lepol.[B@]
                                      AND pol.[PolRef@] = lepol.[Ref@] + ''
                                      + lepol.[Notes1]
        LEFT OUTER JOIN [dbo].[ic_brcledger] AS l ON pol.[B@] = l.[B@]
                                                     AND pol.[PolRef@] = l.[PolRef@]
        LEFT OUTER JOIN [dbo].[T_FIG_New_PolicyBrandRegister] AS pbr ON pol.[B@] = pbr.[Branch]
                                                              AND pol.[PolRef@] = pbr.[PolicyRef]
        LEFT OUTER JOIN ( SELECT    [B@] ,
                                    [PolRef@] ,
                                    [Reg] AS [Reg]
                          FROM      [dbo].[ic_BD_CF] AS cf
                          UNION
                          SELECT    [B@] ,
                                    [PolRef@] ,
                                    [Reg_mark] AS [Reg]
                          FROM      [dbo].[ic_BD_TW1] AS tw1
                        ) AS regTbl ON pol.[B@] = [regTbl].[B@]
                                       AND pol.[PolRef@] = [regTbl].[PolRef@]
        LEFT OUTER JOIN [dbo].[ic_BD_PQ11] AS pq11 ON pol.[B@] = [pq11].[B@]
                                                      AND pol.[PolRef@] = [pq11].[PolRef@]
        LEFT OUTER JOIN [dbo].[ic_BD_PQ02] AS pq02 ON pol.[B@] = [pq02].[B@]
                                                      AND pol.[PolRef@] = [pq02].[PolRef@]
WHERE   pol.[Ptype] IN ( 'TW' , 'PC' , 'MT' )
        AND pol.[B@] <> 1
        AND pbr.[Affinity] IN ('Autosaint' , 'Ladybird' , 'Motor Trade' , 'Special Risks' )
        --AND (lepol.[PolRef@] IS NULL)
        AND pol.[Term_date] IS NULL
        AND pol.[Rdat] >= dbo.[Today]()
        --AND lepol.[Insco] = 'Winn'
        AND pol.[Insco] <> 'Hawkwell'
        --AND l.[Trantype] IN ( 'New Business' , 'Renewal' , 'Transfrd NB' )
        --AND l.[Ledger_dt] BETWEEN '20140802' AND dbo.[Today]()-1
        AND NOT EXISTS ( SELECT *
                         FROM   [FreshSystems].dbo.WINNS_Live_Policy_List AS tfwpl
                         WHERE  pol.[B@] = [tfwpl].[Fresh Branch]
                                AND pol.[PolRef@] = [tfwpl].[Fresh Ref])   

Open in new window


Basically I want to SELECT any records into a temporary table using the above statement but I don't want to select them if both the B@ and PolRef/Fresh Ref exists in the WINNS_Live_Policy_List.

It is only showing me 2 cases to insert on 4 pieces of business and after doing a simply 'SELECT' I can see that there are no records with the Fresh Ref & B@ that matches the 4 to go in so it should select 4 cases.

Help!
0
Comment
Question by:Lynchie435
5 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40311573
Are you sure the issue is with the not exists clause and not with another part of your select statement?
It would be helpful if you could provide some sample data.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40311642
Test your logic with a simplified SQL. E.g.
SELECT DISTINCT
        [c].[Name] AS 'Name' ,
        LEFT([c].[Forenames], ISNULL(NULLIF(CHARINDEX(' ', REVERSE([c].[Forenames])) - 1, -1), LEN([c].[Forenames]))) AS 'First Name' ,
        CASE WHEN CHARINDEX(' ', [c].[Forenames], 1) > 1 THEN SUBSTRING([c].[Forenames], CHARINDEX(' ', [c].[Forenames], 1) + 1, 20)
             ELSE ' '
        END AS 'Middle Names' ,
        RIGHT(c.Name, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(c.Name)) - 1, -1), LEN(c.Name))) AS Surname ,
        [c].[Addr1] AS 'Address line 1' ,
        [c].[Addr2] AS 'Address line 2' ,
        [c].[Addr3] AS 'Address line 3' ,
        [c].[Addr4] AS 'Address line 4' ,
        [c].[Pcode] AS 'Postcode' ,
        pol.[Insco] AS 'Insurer' ,
        [pol].[Polno] AS 'Insurer Ref' ,
        [pol].[B@] AS 'Fresh Branch' ,
        [pol].[PolRef@] AS 'Fresh Ref' ,
        [pol].[Rdat] AS 'Renewal Date' ,
        [pol].[Idat] AS 'Inception Date' ,
        'N' AS [Status] ,
        ( SELECT    COUNT(*)
          FROM      [FreshSystems].dbo.WINNS_Live_Policy_List AS tfwpl
          WHERE     pol.[B@] = [tfwpl].[Fresh Branch]
                    AND pol.[PolRef@] = [tfwpl].[Fresh Ref]
        ) AS Cnt
FROM    [dbo].[ic_yyclient] AS c
        LEFT OUTER JOIN [dbo].[ic_brpolicy] AS pol ON [c].[B@] = [pol].[B@]
                                                      AND [c].[Ref@] = [pol].[Ref@]
WHERE   pol.[Ptype] IN ( 'TW', 'PC', 'MT' )
        AND pol.[B@] <> 1
        AND pol.[Term_date] IS NULL
        AND pol.[Rdat] >= dbo.[Today]()
        AND pol.[Insco] <> 'Hawkwell';
        

Open in new window

0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40311646
Maybe you want to change the AND for an OR. Also you don't need the '*' so for performance issues I changed it to a constant value (1) :
.
.
.
AND NOT EXISTS ( SELECT 1
                         FROM   [FreshSystems].dbo.WINNS_Live_Policy_List AS tfwpl
                         WHERE  pol.[B@] = [tfwpl].[Fresh Branch]
                                OR pol.[PolRef@] = [tfwpl].[Fresh Ref])   

Open in new window

0
 
LVL 33

Expert Comment

by:ste5an
ID: 40311677
Also you don't need the '*' so for performance

It's a myth. The column selection is not necessary for the EXISTS predicate, thus the optimizer ignores it.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 40316021
I have no idea what you are trying to do but the following are not OUTER JOINs, they are INNER JOINs:
LEFT OUTER JOIN [dbo].[ic_brpolicy] AS pol ON [c].[B@] = [pol].[B@]  AND [c].[Ref@] = [pol].[Ref@]
LEFT OUTER JOIN [dbo].[T_FIG_New_PolicyBrandRegister] AS pbr ON pol.[B@] = pbr.[Branch]  AND pol.[PolRef@] = pbr.[PolicyRef]

if you are wondering why the following conditions in your WHERE clause are the answer
WHERE   pol.[Ptype] IN ('TW', 'PC', 'MT')
        AND pol.[B@] <> 1
        AND pbr.[Affinity] IN ('Autosaint', 'Ladybird', 'Motor Trade', 'Special Risks')
        AND pol.[Term_date] IS NULL
        AND pol.[Rdat] >= dbo.[Today]()
        AND pol.[Insco] <> 'Hawkwell'

So if you really want an OUTER JOIN you need to code it correctly.  If on the other hand you want an INNER JOIN, why not write INNER JOIN instead, to avoid any future confusion.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now