Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    4

    Unanswered: select query help...

    Hi i'm trying to do a query where it scans through all the i_user fields and groups the results of the i_status, and give me the results if an i_user does not have a i_status of 2. example below would return 010000 since i_status 2 is not present out of all the entries. the query has to look at the results as a group and not an individual entry.

    tbl: rate_pictures

    i_user i_status
    010000 1
    010000 1

    i_user i_status
    130000 1
    130000 1
    130000 1
    130000 2

    i_user i_status
    150000 1
    150000 1
    150000 1
    150000 2

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT i_user 
      FROM rate_pictures
    GROUP
        BY i_user
    HAVING COUNT(CASE WHEN i_status = 2
                      THEN 'hiccough'
                      ELSE NULL END) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    4
    Quote Originally Posted by r937 View Post
    Code:
    SELECT i_user 
      FROM rate_pictures
    GROUP
        BY i_user
    HAVING COUNT(CASE WHEN i_status = 2
                      THEN 'hiccough'
                      ELSE NULL END) = 0
    what does 'hiccough' never seen that used in a sql statement before

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by acctman View Post
    what does 'hiccough' never seen that used in a sql statement before
    thank you, thank you very much, i am glad that you asked me because not everyone does, when i post this solution

    you could substitute 'hiccup' if that helps

    actually you could substitute any non-null value, and sometimes i write it like this --
    Code:
    HAVING COUNT(CASE WHEN i_status = 2
                      THEN 937 END) = 0
    and of course the ELSE value is NULL by default

    now all you have to do is take this knowledge of NULL versus not NULL, armed with the fact that aggregate functions do not include NULLs, and consider that the aggregate function in this instance is COUNT...

    let me know if that makes sense
    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
  •