Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2004
    Posts
    14

    Unanswered: Omitting records from query based on field value

    Is it possible to omit records from a query based on the values of a field in multiple records...

    I have a table with an ID and a transactionEvent. The transaction event has multiple values like 1, 11. So my table looks something like:

    ID transactionEvent
    --------------------------
    1 1
    1 11
    1 2
    1 22
    1 3
    1 4
    1 5

    My logic states: If (1 and 11 exists for the same ID) then remove both records, whole looping thru the whole table.

    Does this sound possible.

    Regards
    Last edited by DMaxJ; 08-14-08 at 12:37.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is the natural key for the table? Can an ID have more than one record with the same transactionevent code?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by DMaxJ
    If (1 and 11 exists for the same ID) then remove both records
    Code:
    DECLARE @my_table table (
       id    int
     , event int
    )
    
    INSERT INTO @my_table (id, event)
              SELECT 1, 1
    UNION ALL SELECT 1, 11
    UNION ALL SELECT 1, 1
    UNION ALL SELECT 1, 11
    UNION ALL SELECT 1, 3
    UNION ALL SELECT 1, 4
    UNION ALL SELECT 1, 5
    
    SELECT * FROM @my_table
    
    DELETE @my_table
    FROM   @my_table As [a]
     INNER
      JOIN (
            SELECT id
                 , event
            FROM   @my_table
            GROUP
                BY id
                 , event
            HAVING Count(*) > 1
           ) As [b]
        ON a.id = b.id
       AND a.event = b.event
    
    SELECT * FROM @my_table
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think I've misenterpreted the question... damn.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2004
    Posts
    14
    Quote Originally Posted by blindman
    What is the natural key for the table? Can an ID have more than one record with the same transactionevent code?
    ID is a foreign key in this table - and, no, ID cannot have more than one record with the same transactionEvent.

    ID can only have one of eight different transactionEvents in the table.

    Regards

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by DMaxJ
    ID cannot have more than one record with the same transactionEvent
    The sample data you posted begs to differ (hence my earlier confusion)
    Quote Originally Posted by DMaxJ
    ID transactionEvent
    --------------------------
    1 1
    1 11
    1 1
    1 11
    1 3
    1 4
    1 5
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2004
    Posts
    14
    Yes - sorry - my mistake due to my confusion...

    I will correct it in the original message... So the only records left would be

    ID transactionEvent
    --------------------------
    1 3
    1 4
    1 5

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're still confusing now - surely id=1 event=22 would still be left?
    Code:
    DECLARE @my_table table (
       id    int
     , event int
    )
    
    INSERT INTO @my_table (id, event)
              SELECT 1, 1
    UNION ALL SELECT 1, 11
    UNION ALL SELECT 1, 2
    UNION ALL SELECT 1, 22
    UNION ALL SELECT 1, 3
    UNION ALL SELECT 1, 4
    UNION ALL SELECT 1, 5
    
    SELECT * FROM @my_table
    EDIT: my "solution" was tosh
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2004
    Posts
    14
    Hmmmm - I think that I follow your logic by creating a new table and using UNION ALL to only gather all values among the ID and transactionEvent.

    However, there are over 2000 records. Is there a recursive way to code this?

    Regards

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Avoid recursion in SQL. The UNION ALL, or a DISTINCT clause, will do fine.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Aug 2008
    Posts
    1
    I think GeorgeV was only doing the unions to build up some sample data. I have done the same below but only the query at the bottom is the important bit, the rest just creates a test table.

    If I understand the question, you want to write a query against the your events table, which brings back all rows except where the eventid is 1 or 11 and there is a another record for the same id with the event id also 1 or 11?

    You couldn't avoid recursion, as far as I can see, because you need to know what is in multiple rows to know whether to exclude them

    DECLARE @my_table table (
    id int
    , event int
    )

    INSERT INTO @my_table (id, event)
    SELECT 1, 1
    UNION ALL SELECT 1, 11
    UNION ALL SELECT 2, 1
    UNION ALL SELECT 2, 11
    UNION ALL SELECT 1, 3
    UNION ALL SELECT 1, 4
    UNION ALL SELECT 1, 5
    UNION ALL SELECT 2, 5
    UNION ALL SELECT 3, 1
    UNION ALL SELECT 3, 3
    UNION ALL SELECT 1, 2
    UNION ALL SELECT 1, 22

    SELECT * FROM @my_table -- show everything

    SELECT * FROM @my_table a
    where not (event in (1,11) and exists (
    select 1 from @my_table b
    where b.id= a.id
    and event in (1,11)
    and a.event != b.event)
    ) -- filtered results

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's not really recursion. Just a join, which is fair game for TSQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  ptp  20080814  See http://www.dbforums.com/showthread.php?t=1633045
    
    CREATE TABLE #PatP (
       ID		     INT
    ,  transactionEvent  INT
       )
    
    INSERT INTO #PatP (
       ID, transactionEvent
       ) SELECT 1, 1
          UNION SELECT 1, 11
          UNION SELECT 1, 2
          UNION SELECT 1, 22
          UNION SELECT 1, 3
          UNION SELECT 1, 4
          UNION SELECT 1, 5
    
    SELECT *
       FROM #PatP AS a
       WHERE NOT EXISTS (SELECT *
          FROM #PatP AS b
          WHERE  a.ID = b.ID
             AND 11 IN (a.transactionEvent / b.transactionEvent
    ,           b.transactionEvent / a.transactionEvent))
    
    DROP TABLE #PatP
    -PatP

  14. #14
    Join Date
    Apr 2004
    Posts
    14
    Why is recursion a bad things in T-SQL? I found that I can avoid endless loops by using the MAXRECURSION command.

    I am still at a loss for solving this problem. So, I have made new tables containing the ID and the transactionEvent only:

    Table 1
    ID transactionEvent
    -------------------------
    1 1
    2 1
    3 1
    4 1
    5 1
    [more rows]

    Table 2
    ID transactionEvent
    -------------------------
    1 11
    2 11
    6 11
    7 11
    8 11
    [more rows]

    So I have 10 tables each with multiple ID's with the same transactionEvent. I arrived at this point thinking that I may be able to program an SQL script to OR maybe a JOIN (not sure which JOIN to use yet) could help.

    Regards

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Recursion is inefficient in TSQL compared to set-based algorithms.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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