MySQL Newb question

El Gabito · #1 (**permalink**) 11-07-06, 14:36

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!

GongXi · #2 (**permalink**) 11-08-06, 00:58

what is the final result you want?
return user that have all level = 0 only?

r937 · #3 (**permalink**) 11-08-06, 07:44

Code:

select user_id
  from data1
group
    by user_id
having sum(level) = 0

El Gabito · #4 (**permalink**) 11-08-06, 08:26

Quote:

Originally Posted by r937

Code:

select user_id
  from data1
group
    by user_id
having sum(level) = 0

I think that worked! Thanks!

El Gabito · #5 (**permalink**) 11-09-06, 13:17

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!!!

r937 · #6 (**permalink**) 11-09-06, 13:42

and the sum of their amount from data2... what?

El Gabito · #7 (**permalink**) 11-09-06, 13:46

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

Internet Services

Web Development

Digital Marketing

Consumer Tech