none
SQL 2008 R2 - how to show records based on a sub-select (2 fields)

    Question

  • Hi,

    I have one table as following:

    TBL_LOGS_HMAIL
    #############
    EventType (nvarchar(5),null)
    EventID (int, null)
    TransactionID (int, null)
    TimeStamp (datetime, null)
    IPAdress (varchar(255), null)
    Details (ntext, null) 


    ----------------------------------------------------

    Using the query below, I´m able to get the combination of all EventID and TransactionID

    select EventID, TransactionID from [TBL_LOGS_HMAIL]
    where
    (
    Details LIKE N'SENT: MAIL FROM:<john@domain.com.br>'
    )

    ---------------------------------------------

    How to show all records from that table but restricted for the combination above??

    Sunday, June 23, 2013 1:14 AM

Answers

  • Your question is not crystal clear, but I guess that you want to see all rows for those (EventID, TransactioID) for which at least one row has Detail equal to that SENT-string.

    SELECT *
    FROM   TBL_LOGS_HMAIL a
    WHERE  EXISTS (SELECT *
                   FROM   TBL_LOGS_HMAIL b
                   WHERE  a.EventID = b.EventID
                     AND  a.TransactionID = b.TransactionID
                     AND  b.Details LIKE N'SENT: MAIL FROM:<john@domain.com.br>')

    By the way, the ntext data type is deprecated, and you should use nvarchar(MAX) data type instead. (Unless you are stuck on SQL 2000.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, June 23, 2013 9:10 AM

All replies

  • Please post sample data + desired result. Always state what version you are using.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, June 23, 2013 7:24 AM
    Answerer
  • Your question is not crystal clear, but I guess that you want to see all rows for those (EventID, TransactioID) for which at least one row has Detail equal to that SENT-string.

    SELECT *
    FROM   TBL_LOGS_HMAIL a
    WHERE  EXISTS (SELECT *
                   FROM   TBL_LOGS_HMAIL b
                   WHERE  a.EventID = b.EventID
                     AND  a.TransactionID = b.TransactionID
                     AND  b.Details LIKE N'SENT: MAIL FROM:<john@domain.com.br>')

    By the way, the ntext data type is deprecated, and you should use nvarchar(MAX) data type instead. (Unless you are stuck on SQL 2000.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, June 23, 2013 9:10 AM
  • You´re my hero!

    Worked like a charm!

    tks a lot!

    Monday, June 24, 2013 1:50 PM