Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    64

    Unanswered: simple? multple ID matching varied content query.

    this shoud be simple, but my brain's hurting.

    I have a table with 3 fields...

    IDnum,content,application

    eg
    123,stop,fb
    123,start,fb
    123,continue,fb
    123,fault,fb
    123,start,gt
    123,stop,gt
    123,continue,gt
    123,start,ha
    321,stop,fb
    321,start,fb
    321,continue,fb
    321,fault,fb
    321,start,gt


    I need to know All those unique IDs that have "start" against them, but have not got "stop" against them, I'm not interested in the other content strings.

    I need to query it based on a specific application ID.

    there will be many records from each IDnum, but only one start for each. I need to know all those IDnums that have "started" and not "stopped"

    tia

    a
    Last edited by arpoodle; 02-23-06 at 10:53. Reason: clarifying

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quick and dirty ... ad hoc use

    Code:
     
    select distinct a.IDnum 
    from <tablename> a
    where a.content = 'start'
      and a.IDnum not in
          (select distinct b.IDnum 
           from <tablename> b
           where b.content = 'stop')

    -- This is all just a Figment of my Imagination --

Posting Permissions

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