Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    5

    Unanswered: MySQL Newb question

    I have a field (data1) where there is a user_id, level, and key_id. Each user can have multiple key_id's, and each key_id has a unique value 0-4.

    What I am looking to do is return ONE user_id ONLY if ALL of their levels are 0. So for the below table, the only result returned should be 55012, because all three levels are 0.



    Code:
    user_id | key_id | level
    ----------------------
    55012  |   1495  |  0
    55012  |   2495  |  0
    55012  |   3495  |  0
    55212  |   1491  |  0
    55212  |   2492  |  3
    55212  |   3493  |  1
    TIA for the help!

  2. #2
    Join Date
    Jun 2004
    Posts
    57
    what is the final result you want?
    return user that have all level = 0 only?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select user_id
      from data1
    group
        by user_id
    having sum(level) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2006
    Posts
    5
    Quote Originally Posted by r937
    Code:
    select user_id
      from data1
    group
        by user_id
    having sum(level) = 0

    I think that worked! Thanks!

  5. #5
    Join Date
    Nov 2006
    Posts
    5
    Okay, you guys were very helpful, and I thought I could figure this out on my own, but I think that last part was actually the easy part.

    I have another field shown below (data2):

    Code:
    user_id | id | amount
    ----------------------
    55012  |   1495  |  0.01
    55212  |   2495  |  0.10
    55012  |   3495  |  0.81
    55012  |   4495  |  0.25
    55212  |   5495  |  0.76
    55212  |   6495  |  0.45

    I am looking to return all users from both tables where all of their "level" are zero and the sum of their amount from data2.


    Thanks!!!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and the sum of their amount from data2... what?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2006
    Posts
    5
    Quote Originally Posted by r937
    and the sum of their amount from data2... what?

    Okay, 55012 is the only result from the first part of it, right? Then I want to say, what's the sum of the "amount" from data2 for user_id 55012. So the final result should be

    Code:
    user_id | amount 
    ---------------
    55012  |   0.92

Posting Permissions

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