Results 1 to 7 of 7

Thread: Find value that is not included in a group of rows.

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Find value that is not included in a group of rows.

    Hello.

    I have

    WebPayTN_lngPayTransNumber, WebPayLog_strStep
    1 BankResponseRaw
    1 NOTBankResponseRaw
    1 EXCEL
    1 TU

    and

    WebPayTN_lngPayTransNumber, WebPayLog_strStep
    2 NOTBankResponseRaw
    2 EXCEL
    2 TU

    I want to find the WebPayTN_lngPayTransNumber that do not have a WebPayLog_strStep named BankResponseRaw
    Of course if i do "and WebPayLog_strStep <> ' BankResponseRaw' " I will also bring the WebPayTN_lngPayTransNumber "1" just without the BankResponseRaw line.
    But I wouldn't bring that row with just the missing 1 -- BankResponseRaw though I only need to bring the id 2 row.

    I'm trying to do that with CTE but I'm stuck. I would expect to get some NULLS so I can eliminate them but it will not work.

    Code:
    With CTE (WebPayTN_lngPayTransNumber,WebPayLog_strStep)
    
    AS  
    -- Define the CTE query.  
    (
    select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog  
    where WebPayLog_strStep ='BankResponseRaw'
    --group by WebPayTN_lngPayTransNumber,WebPayLog_strStep
    )  
    
    
    
    select L.WebPayTN_lngPayTransNumber,CTE.WebPayTN_lngPayTransNumber, CTE.WebPayLog_strStep, L.WebPayLog_strStep
     from CTE Left join tblWebPaymentModuleLog L on CTE.WebPayTN_lngPayTransNumber= L.WebPayTN_lngPayTransNumber
     where CTE.WebPayTN_lngPayTransNumber= L.WebPayTN_lngPayTransNumber AND CTE.WebPayLog_strStep <> L.WebPayLog_strStep
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Find value that is not included in a group of rows.

    Maybe something like this?


    SELECT
    WebPayLog_strStep
    FROM dbo.test1
    EXCEPT
    SELECT
    WebPayLog_strStep
    FROM dbo.test2



    SELECT
    WebPayLog_strStep
    FROM dbo.test2
    EXCEPT
    SELECT
    WebPayLog_strStep
    FROM dbo.test1
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Find value that is not included in a group of rows.

    That will give me a empty table
    with both:

    Code:
    SELECT
    WebPayLog_strStep
    FROM  tblWebPaymentModuleLog
    EXCEPT
    SELECT
    WebPayLog_strStep
    FROM tblWebPaymentModuleLog
    or

    Code:
    With CTE (WebPayTN_lngPayTransNumber,WebPayLog_strStep)
    
    AS  
    -- Define the CTE query.  
    (
    select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog  
    where WebPayLog_strStep ='BankResponseRaw'
    --group by WebPayTN_lngPayTransNumber,WebPayLog_strStep
    )  
    
    
    SELECT
    WebPayLog_strStep
    FROM  CTE
    EXCEPT
    SELECT
    WebPayLog_strStep
    FROM tblWebPaymentModuleLog
    the key is the "BankResponseRaw"
    The select should give me the number "2" rows because they do not contain the "BankResponseRaw"
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Find value that is not included in a group of rows.

    Code:
    WebPayTN_lngPayTransNumber	WebPayLog_strStep
    100000001	StoreTransDetails
    100000001	GetPurchaseInfo
    100000001	RedirectionInfo
    100000001	GetPurchaseInfo
    100000001	RedirectionInfo
    100000001	GetPurchaseInfo
    100000001	RedirectionInfo
    100000002	StoreTransDetails
    100000001	GetPurchaseInfo
    100000001	RedirectionInfo
    100000003	StoreTransDetails
    100000003	GetPurchaseInfo
    100000003	RedirectionInfo
    100000003	GetPurchaseInfo
    100000003	RedirectionInfo
    100000004	StoreTransDetails
    100000004	GetPurchaseInfo
    100000004	RedirectionInfo
    NULL	BankResponseRaw
    100000004	BankResponseRaw
    100000004	GetPurchaseResult
    NULL	GetPaymentResult
    100000005	StoreTransDetails
    100000005	GetPurchaseInfo
    100000005	RedirectionInfo
    this is a real sample of the select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Find value that is not included in a group of rows.

    there is just one table involved, right?

    somthing like that?
    Code:
    select WebPayTN_lngPayTransNumber,max(case when WebPayLog_strStep = 'BankResponseRaw' THEN 1 ELSE 0 END) AS HasBankResponseRaw
    from tbl
    group by WebPayTN_lngPayTransNumber
    having HasBankResponseRaw = 0
    ?

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Find value that is not included in a group of rows.

    Quote Originally Posted by sapator View Post
    That will give me a empty table
    with both:

    Code:
    SELECT
    WebPayLog_strStep
    FROM  tblWebPaymentModuleLog
    EXCEPT
    SELECT
    WebPayLog_strStep
    FROM tblWebPaymentModuleLog
    or

    Code:
    With CTE (WebPayTN_lngPayTransNumber,WebPayLog_strStep)
    
    AS  
    -- Define the CTE query.  
    (
    select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog  
    where WebPayLog_strStep ='BankResponseRaw'
    --group by WebPayTN_lngPayTransNumber,WebPayLog_strStep
    )  
    
    
    SELECT
    WebPayLog_strStep
    FROM  CTE
    EXCEPT
    SELECT
    WebPayLog_strStep
    FROM tblWebPaymentModuleLog
    the key is the "BankResponseRaw"
    The select should give me the number "2" rows because they do not contain the "BankResponseRaw"
    I think you were close with that last one... but here's why it didn't work:
    your CTE is sourced by the table for all records with 'BankResponseRaw' ... So that includes row 1.
    You're then selecting frmo that CTE (which has one row) and then EXCEPTING everything in the table... since the table has 1 in it... it gets excluded from your query results....

    You had it upside down:
    Code:
    With CTE (WebPayTN_lngPayTransNumber,WebPayLog_strStep)
    
    AS  
    -- Define the CTE query.  
    (
    select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog  
    where WebPayLog_strStep ='BankResponseRaw'
    --group by WebPayTN_lngPayTransNumber,WebPayLog_strStep
    )  
    
    
    SELECT
    WebPayTN_lngPayTransNumber
    FROM  tblWebPaymentModuleLog
    EXCEPT
    SELECT
    WebPayTN_lngPayTransNumber
    FROM CTE
    And you want the WebPayTN_lngPayTransNumber not the WebPayLog_strStep ....
    You'll probably want the DISTINCT results from that too...

    try flipping your query like I did and select the other fields, see if that works.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Find value that is not included in a group of rows.

    Hi.
    Thanks I will have to remake the tables when I get the chance and try this, as we are constantly on and off from work due to the covid BS.
    So I just got to the office today from September 8th that I posted and I hear there would be a lockdown again in Athens, so I might leave again to EVIA Island that the is no lockdown for now, I appreciate the answers, will post back if I get the chance to recreate the issue.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width