Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2010
    Posts
    6

    Angry Unanswered: Query question - must be easy for someone who knows

    Thanks for reading this and hopefully trying to help me. I obviously do not do SQL for a living - hence the need for some help please.

    Table looks like this:

    MasterID--------DetailID--------Process
    1234------------56-------------Step 1 completed
    1234------------56-------------Step 2 completed
    1234------------56-------------Step 3 completed
    2476------------80-------------Step 1 completed
    2476------------80-------------Step 2 failed
    2476------------90-------------Step 1 completed
    2476------------90-------------Step 2 completed
    2476------------90-------------Step 3 completed

    I ma trying to figure out how to give me the MasterID of any Process that contians the word failed in it.

    SELECT
    TABLE.MasterID

    FROM
    TABLE

    WHERE
    TABLE.Process NOT LIKE 'failed'

    The problem I have is that if the same MasterID also contains a COMPLETED, it will give me that Master ID.

    I am trying to find out if I can get the MasterID if ANY PROCESS is FAILED.

    I hope I am asking this correctly - and I sincerely appreciate any guidance!! Thank you!!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why are you saying "NOT LIKE". What does "not" indicate?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2010
    Posts
    6
    Apologies - I think while trying to explain my problem - I confused it.

    My train of thought is that I do not want ANY MASTERID's that contain 'FAILED'.

    My problem is that if a MASTERID has 1 record for a COMPLETED and 1 record of a FAILED and I ask for a return of all MASTERID not like FAILED, I will still get the MASTERID with a FAIL, because it also has a complete.

    I am unable to use 1 criteria to filter by if both criteria exist under 1 MASTERID

    ....phew - I am envious of you folks that understand this stuff so well - SQL is like double-speak...twice!

    Thank you for your time!

  4. #4
    Join Date
    Jul 2010
    Posts
    6
    To put my question in simple terms....

    A MASTERID can have many Processes in it. What I want to do is to filter out any MASTERID with specific text in it. I think the problem is that because SQL is so specific, just because the MASTERID has 1 process with my text to be filtered on, the other processes of the same MASTERID negate my filter.

    Perhaps that clears my question up...or makes it worse.

    Thanks for the time and patience!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Thraysmachus was trying to subtly point out that the "NOT" is a major part of your problem. You also need percent signs which are the wildcard match for the LIKE clause. You actually want something like:
    Code:
    SELECT TABLE.MasterID
       FROM TABLE
       WHERE TABLE.Process LIKE '%failed%'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    select * 
    from  TABLE
    where TABLE.MasterID not in (select MasterID 
          from TABLE
          where TABLE.process like '%failed%')
    Last edited by MCrowley; 07-20-10 at 15:10. Reason: Too many "not"s

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT humpty.MasterID
         , humpty.DetailID
         , humpty.Process
      FROM daTable AS humpty
    LEFT OUTER
      JOIN daTable AS dumpty
        ON dumpty.MasterID = humpty.MasterID
       AND dumpty.Process LIKE '%failed%'
     WHERE dumpty.MasterID IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2010
    Posts
    6
    Thank you all for the time....Let me try this out.

    I appreciate your assistance!

  9. #9
    Join Date
    Jul 2010
    Posts
    6
    Quote Originally Posted by r937 View Post
    Code:
    SELECT humpty.MasterID
         , humpty.DetailID
         , humpty.Process
      FROM daTable AS humpty
    LEFT OUTER
      JOIN daTable AS dumpty
        ON dumpty.MasterID = humpty.MasterID
       AND dumpty.Process LIKE '%failed%'
     WHERE dumpty.MasterID IS NULL
    Um....I would like to try this code...

    WTH is humpty, dumpty and DaTable supposed to be in real life? Thanks again!

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Dizastrous View Post
    WTH is humpty, dumpty and DaTable supposed to be in real life?
    in real life, daTable is the name of your table

    in your first post, you called it TABLE (which of course is an invalid name)

    my query performs a self-join, and as you know, in any self-join you must supply at least one and preferably two table alias names -- that's humpty and dumpty

    so all you have to change is daTable, replacing it with your actual table name

    humpty and dumpty will of course work correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2010
    Posts
    6
    Thank you very much....I do believe I got this to work with that code!

    Much appreciated!!!

Posting Permissions

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