Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65

    Unanswered: Conditional insert based on MAX number of field

    Hi!

    I have a table called DB1 that contains this:

    MID
    IIN
    NUM_EVENTS
    DATE

    MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use the WHERE clause.

    Where NUMEVENTS = 1

    No offense, but these are pretty basic questions you are asking. You really need to crack open Books Online and read the sections on SELECT, UPDATE, INSERT, and DELETE statements, with all their various options. You could also benefit from perusing the various text and concatenation functions available.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I guess you mean those with NUM_EVENTS = 1 and only 1? Otherwise Blindman is right - this is 101 stuf.

    Anyway - several ways. One might be (I am accounting for the possiblilty of the NUM_EVENTS record being deleted, so there might still be n records but none with NUM_EVENTS = 1):
    Code:
    SELECT A.* 
    FROM DB1 A INNER JOIN 
    (SELECT MID, IIN FROM DB1 GROUP BY MID, IIN HAVING COUNT(NUM_EVENTS) = 1) B 
    ON A.MID = B.MID AND A.IIN = B.IIN
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65
    Quote Originally Posted by blindman
    Use the WHERE clause.

    Where NUMEVENTS = 1

    No offense, but these are pretty basic questions you are asking. You really need to crack open Books Online and read the sections on SELECT, UPDATE, INSERT, and DELETE statements, with all their various options. You could also benefit from perusing the various text and concatenation functions available.
    I think my question was misunderstood.
    DB1 has MID, IIN and NUM_EVENTS as the key. Each MID, IIN combination will have at least 1 record but can have more.

    Ex. MID IIN NUM_EVENTS
    01 234567 1
    01 234567 2
    01 126467 1

    In this example, you can see that there are 2 events for this particular IIN(234567), what I want to do is create a query that only selects those MID/IIN combinations with only 1 NUM_EVENT(in this example, 126467)

  5. #5
    Join Date
    Dec 2005
    Posts
    2
    try this..

    Select MID,IIN, count(NUM_EVENTS)
    from tablename
    groupby MID,IIN
    having count(num_events) =1

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by poots
    SELECT A.*
    FROM DB1 A INNER JOIN
    (SELECT MID, IIN FROM DB1 GROUP BY MID, IIN HAVING COUNT(NUM_EVENTS) = 1) B
    ON A.MID = B.MID AND A.IIN = B.IIN

    Quote Originally Posted by shallu1_gupta
    try this..

    Select MID,IIN, count(NUM_EVENTS)
    from tablename
    groupby MID,IIN
    having count(num_events) =1
    Oops - I put it down to late night posting. K.I.S.S.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65
    Thanks guys!

Posting Permissions

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