CODEWITHSUNDEEP
phpmysql
rob melino
rob melino

Reputation: 781

find top 3 values with an inner join

I have a table called users_preferred_zips that looks like this:

 username  |  price  |   zip   |  program  |  active
-----------+---------+---------+-----------+---------
 joe       |    5    |  92108  |  dog      |    1
 tom       |    7    |  92108  |  dog      |    1
 mary      |    5    |  92108  |  dog      |    1
 paul      |    6    |  92108  |  dog      |    1
 ron       |    6    |  92108  |  dog      |    1

I have another table called users that looks like this

 username  |  balance
-----------+----------
 joe       |    10
 tom       |    12
 mary      |    2
 paul      |    14
 ron       |    3

I need a query to pull AND sum the 3 highest values from the users_preferred_zips table where the username from the users table has a balance value greater than or equal to 5. I know i need to do some sort of inner join but my query below is not working. Here is the query i have:

SELECT SUM(price) AS SumOfTopValues
FROM (
    SELECT users_preferred_zips . * , users.last_purchase, users.lesson_type, users.pref_acct_balance
    INNER JOIN users ON ( users_preferred_zips.username = users.username ) 
    WHERE users_preferred_zips.zip =  '92108'
    AND users_preferred_zips.program =  'dog'
    AND users_preferred_zips.active =  1
    AND users.pref_acct_balance >= '5'
    ORDER BY price DESC
    LIMIT 3
) AS sub

So the correct query would pull the following:

3 highest:

joe   |  5
tom   |  7
paul  |  6

Sum of 3 highest values = 18

I feel like this should be pretty simple but i'm having a tough time! Thanks for your help

Upvotes: 2

Views: 251

Answers (1)

li-on
li-on

Reputation: 539

You can check this using:

SELECT SUM(price) AS SumOfTopValues
FROM users_preferred_zips
WHERE username IN (
  SELECT username
  FROM users
  WHERE pref_acct_balance >= 5
)

Upvotes: 2

Related Questions