Select from view where not exists in table

  • barry.nielson

    SSC Eights!

    Points: 904

    Hi all,

    I am attempting to return only values that do not exist by comparing a view to a table.

    SELECT 
    *
    FROM V_MyView Q

    WHERE NOT EXISTS (
    SELECT 1
    FROM Tbl_MyTable T
    WHERE
    T.[CUstomerID] = Q.ID AND
    T.[ActivityID] = 58 AND
    T.[RelatedReferenceID] = Q.ReferenceID AND
    -- T.[RelatedReferenceType] = Q.ArrangementType AND -- Does not work
    T.[RelatedReferenceType] = 'TypeA' OR T.[RelatedReferenceType] = 'TypeB' AND -- This works instead
    T.[WorkItemType] = 'Account' AND
    CONVERT(VARCHAR(10), T.[Due Date], 111) = CONVERT(VARCHAR(10), Q.Enddate, 111) -- Does not work
    )

    The query runs, but it's always returning the wrong result.  It should return 0, or possibly 5 records at most, but it's returning results like 300-400 results.

    I don't understand why I need to physically specify the string and can't use the name

    -- T.[RelatedReferenceType] = Q.ArrangementType AND -- Does not work
    T.[RelatedReferenceType] = 'TypeA' OR T.[RelatedReferenceType] = 'TypeB' AND -- This works instead

    Also, this is causing a lot of problems as well. I made the mistake of building the field in the table as a Datatime, but now I can't change it because many views and SP's rely on it.  And using the regular field name is not working either.

    CONVERT(VARCHAR(10), T.[Due Date], 111) = CONVERT(VARCHAR(10), Q.Enddate, 111) -- Does not work

    I've tried using Cast, but no joy there.

    In fact, testing every line to find the fault has show the query does the complete opposite of what I want.

    Any help appreciated.

    thanks

     

     

     

    • This topic was modified 6 days, 19 hours ago by  barry.nielson.
  • Erland Sommarskog

    SSC-Insane

    Points: 23737

    I can't say why T.[RelatedReferenceType] = Q.ArrangementType AND does not work for you because, I know nothing about your data, tables and view.

    However, I can answer why

    T.[RelatedReferenceType] = 'TypeA' OR T.[RelatedReferenceType] = 'TypeB'

    gives a different result. This is because now you are radically changing the logic. AND binds tighter than OR, so you have something quite different. Permit me to rearrange the WHERE clause to clarify:

    WHERE      T.[CUstomerID] = Q.ID  
    AND T.[ActivityID] = 58
    AND T.[RelatedReferenceID] = Q.ReferenceID
    AND T.[RelatedReferenceType] = 'TypeA'
    OR
    T.[RelatedReferenceType] = 'TypeB'
    AND T.[WorkItemType] = 'Account'
    AND CONVERT(VARCHAR(10), T.[Due Date], 111) = CONVERT(VARCHAR(10), Q.Enddate, 111)

    You would need to have parentheses:

    (T.[RelatedReferenceType] = 'TypeA' OR T.[RelatedReferenceType] = 'TypeB')

    To get something resemblent of the original.

    When it comes to the date, my recommendation is that you change the columns to be date instead. Yeah, this means that you will have to go back and change things, but if you don't not fix this, you will only be digging deeper and deeper into this hole.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • ScottPletcher

    SSC Guru

    Points: 97979

    Please be more specific on "Does not work" for Q.ArrangementType.  In general you should have no problem referencing columns in the view in a NOT EXISTS, so some other error is happening.  Most likely the column name changed in the view from what it normally was in the original tables used to create the view(?).

    As to the datetime, don't convert the datatime in the lookup table, check for a range of datetime instead.  That's important because it makes the [Due Date] directly searchable for by SQL ("sargable"), but if you perform any function on the column, the direct search possibility is destroyed.

    SELECT 
    *
    FROM V_MyView Q

    WHERE NOT EXISTS (
    SELECT 1
    FROM Tbl_MyTable T
    WHERE
    T.[CUstomerID] = Q.ID AND
    T.[ActivityID] = 58 AND
    T.[RelatedReferenceID] = Q.ReferenceID AND
    -- T.[RelatedReferenceType] = Q.ArrangementType AND -- Does not work
    T.[RelatedReferenceType] IN ('TypeA', 'TypeB') AND -- This works instead /*Changed!*/
    T.[WorkItemType] = 'Account' AND
    T.[Due Date] >= CAST(Q.Enddate AS date) AND /*Changed!*/
    T.[Due Date] < DATEADD(DAY, 1, CAST(Q.Enddate AS date)) /*Changed!*/
    )

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • barry.nielson

    SSC Eights!

    Points: 904

    Hi all, And thanks so far for all your help. It is much appreciated.

    However, nothing I try works to produce a result.  Clearly the issue lies in my not understanding how this is supposed to work.

     SELECT
    T.[CustomerID],
    T.[RelatedReferenceID],
    T.[RelatedReferenceType],
    T.[Due Date]
    FROM [MyDB].[dbo].Tbl_MyTable T
    Where
    T.[CustomerID] = 12345
    AND T.[RelatedReferenceID] = 67891

    The above works and produces a single record.

     SELECT 
    V.[ID],
    V.[ReferenceID],
    V.[ReferenceType],
    V.[Enddate],
    V.[Client Name]
    FROM [MyDB].[dbo].[V_MyView] V
    Where
    V.[ID] = 12345
    AND V.[ArrangementID] = 67891

    The above works to produce Nothing

    SELECT
    T.[CustomerID],
    T.[RelatedReferenceID],
    T.[RelatedReferenceType],
    T.[Due Date]
    FROM [MyDB].[dbo].Tbl_MyTable T
    WHERE Not EXISTS (
    SELECT
    V.[ID],
    V.[ReferenceID],
    V.[ReferenceType],
    V.[Enddate],
    FROM [MyDB].[dbo].[V_MyView] V
    WHERE
    V.[ID] = T.[CustomerID]
    AND V.[ReferenceID] = T.[RelatedReferenceID]
    AND V.[ReferenceType] = T.[RelatedReferenceType]
    AND V.[Enddate] = T.[Due Date]
    )
    AND T.[ClientId] = 12345
    AND T.[RelatedReferenceID] = 67891

    The above even works to produce the expected criteria.

    But that is no good!  I need it to produce the result without the criteria. I need it to find the result.

    There should only be one record produced.  But Instead I'm getting hundreds.

    What is the correct way to do this?

    Thanks again.

  • Erland Sommarskog

    SSC-Insane

    Points: 23737

    The correct way is to explain your problem in a way so that outsiders can understand it. I am sorry, but I am not able to understand what you are trying to achieve. And nor do I know your table or your view.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • ChrisM@Work

    SSC Guru

    Points: 186034

    -- Work with what you can see.
    -- Compare the columns from both sides, add to the matching columns list when you've figured out what's going on.
    SELECT
    T.[CustomerID],
    T.[RelatedReferenceID],
    T.[RelatedReferenceType],
    T.[Due Date],
    '#' '#', -- columns to the left of this are from T, columns to the right are from v.
    v.*
    FROM [MyDB].[dbo].Tbl_MyTable T
    LEFT JOIN [MyDB].[dbo].[V_MyView] V
    ON V.[ID] = T.[CustomerID] -- 12345
    AND V.[ArrangementID] = T.[RelatedReferenceID] -- 67891


    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Matt Miller (4)

    SSC Guru

    Points: 124160

    Try this one

    SELECT 
    V.[ID],
    V.[ReferenceID],
    V.[ReferenceType],
    V.[Enddate]

    FROM [MyDB].[dbo].[V_MyView] V
    except

    SELECT
    T.[CustomerID],
    T.[RelatedReferenceID],
    T.[RelatedReferenceType],
    T.[Due Date]
    FROM [MyDB].[dbo].Tbl_MyTable T

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • barry.nielson

    SSC Eights!

    Points: 904

    Matt - Thanks for this tip.  It appears to offer similar results to alternative methods and is a lot simpler to use.

    I'm curious about how to add additional information into the query not included in the Except.

    It throw an error if I add additional feilds.

    So i want to see the exception, but also additional feilds in the View.  Is that possible?

    ChrisM@Work - I gave your solution a bit of a whirl and it seems awesome.  Except, how can I show where one table does not contain a match?  I.e NULL | NULL | NULL, etc...

    Cheers

     

     

    • This reply was modified 1 day, 14 hours ago by  barry.nielson.
  • Matt Miller (4)

    SSC Guru

    Points: 124160

    The except syntax direct compares matching sets of columns.  That said - the output of the EXCEPT is a recordset, so you could make it a subquery then join back into your table to pull the extract columns.  As with other sub-query techniques, that can start to consue the optimizer so use it sparingly or pop it into a temp table if performance starts to suffer.

     

    Example

    select Tbl_MyTable.extracolumns,
    mismatches. *
    from (
    SELECT
    V.[ID],
    V.[ReferenceID],
    V.[ReferenceType],
    V.[Enddate]

    FROM [MyDB].[dbo].[V_MyView] V
    except

    SELECT
    T.[CustomerID],
    T.[RelatedReferenceID],
    T.[RelatedReferenceType],
    T.[Due Date]
    FROM [MyDB].[dbo].Tbl_MyTable T) mismatches
    join Tbl_MyTable on mismatches.id=Tbl_MyTable.id

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply