Listing rows "missing" information?

bla4free · #1 (**permalink**) 02-23-05, 13:39

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?

Pat Phelan · #2 (**permalink**) 02-23-05, 13:49

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

r937 · #3 (**permalink**) 02-23-05, 14:04

or something like --

Code:

select caseID
  from events 
group
    by caseID
having 0 
     = sum(
        case when eventType = 'Case Opened'
             then 1 else 0 
         end )

Pat Phelan · #4 (**permalink**) 02-23-05, 14:15

My, aren't we feeling deviant today! Heck, Rudy's solution will even work in MySQL!

-PatP

r937 · #5 (**permalink**) 02-23-05, 14:27

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]

Internet Services

Web Development

Digital Marketing

Consumer Tech