Ytukyg

I have this query :

BEGIN 

  DROP TABLE IF EXISTS `tblquota`;

CREATE TABLE IF NOT EXISTS tblquota (id INT AUTO_INCREMENT PRIMARY KEY) AS

     SELECT c.id as user_id

         , c.`email`

         , h.`packageid` as pack_id

         , p.`name` as pack_name

         , max(CASE 

             WHEN `name` = "Basic" THEN '100'

             WHEN `name` = "Silver" THEN '100'

             WHEN `name` = "Gold" THEN '100'

                 ELSE '10'

            END) as quota

     FROM `tblclients` c

     LEFT JOIN `tblhosting` h ON c.id = h.userid 

     INNER JOIN `tblproducts` p ON h.packageid = p.id 

     ORDER BY c.id;

END

In this case, the query will only get the max value for only one customer. I would like to get the value per customer.

table clients:

id | email          | status

----------------------------

1  | user1@mail.com | Active

2  | user2@mail.com | Inactive

3  | user3@mail.com | Active

table tblhosting

id | userid | packageid | domainstatus 

------------------------------------------------

1  | 1      | 3         | Active

2  | 1      | 2         | Active

3  | 2      | 1         | Active

4  | 2      | 2         | Active

5  | 2      | 3         | Inactive

6  | 3      | 1         | Active

table tblproducts

id | name 

-----------

1  | Basic

2  | Silver

3  | Gold

I expect result like :

id | user_id | email          | pack_id | pack_name | quota

-----------------------------------------------------------

1  | 1       | user1@mail.com | 3       | Gold      | 1000

2  | 2       | user2@mail.com | 2       | Silver    | 100

3  | 3       | user3@mail.com | 1       | Basic     | 10

What you are looking for is Max OVER (PARTITION BY user_id)