Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Listing rows "missing" information?

    Here is how part of our database works. We have a table called "Events." Inside the "Events" table, we list various events which takes place for each of our clients. The first event for any client should be "Opened Case." However, some of my employees have not been listing this event so we do not know when the case was actually opened. In theory, all cases should have an "Opened Case" event in them. Is there a way to query that table to find which cases do not contain that event? Or is that more a programming issue? Thanks for any help?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest something like:
    Code:
    SELECT DISTINCT caseID
       FROM events AS a
       WHERE NOT EXISTS (SELECT *
          FROM events AS b
          WHERE  b.caseID = a.caseID
             AND 'Case Opened' = b.eventType)
       ORDER BY caseID
    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    or something like --
    Code:
    select caseID
      from events 
    group
        by caseID
    having 0 
         = sum(
            case when eventType = 'Case Opened'
                 then 1 else 0 
             end )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My, aren't we feeling deviant today! Heck, Rudy's solution will even work in MySQL!

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    deviant? heh

    i was just thinking that yours involves a join (expensive), a NOT EXISTS (expensive), and a sort to remove dupes (expensive)

    mine's just one sort, to do grouping

    [offtopic]hey pat, did you give that Sock Hop channel a try? i was hoping you'd come back with some comment like "oh wow, i love that stuff!" (which was my reaction)[/offtopic]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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