· 7 years ago · Nov 19, 2018, 10:10 AM
1BEGIN
2 DROP TABLE IF EXISTS `tblquota`;
3CREATE TABLE IF NOT EXISTS tblquota (id INT AUTO_INCREMENT PRIMARY KEY) AS
4 SELECT c.id as user_id
5 , c.`email`
6 , h.`packageid` as pack_id
7 , p.`name` as pack_name
8 , max(CASE
9 WHEN `name` = "Basic" THEN '100'
10 WHEN `name` = "Silver" THEN '100'
11 WHEN `name` = "Gold" THEN '100'
12 ELSE '10'
13 END) as quota
14 FROM `tblclients` c
15 LEFT JOIN `tblhosting` h ON c.id = h.userid
16 INNER JOIN `tblproducts` p ON h.packageid = p.id
17 ORDER BY c.id;
18END
19
20id | email | status
21----------------------------
221 | user1@mail.com | Active
232 | user2@mail.com | Inactive
243 | user3@mail.com | Active
25
26id | userid | packageid | domainstatus
27------------------------------------------------
281 | 1 | 3 | Active
292 | 1 | 2 | Active
303 | 2 | 1 | Active
314 | 2 | 2 | Active
325 | 2 | 3 | Inactive
336 | 3 | 1 | Active
34
35id | name
36-----------
371 | Basic
382 | Silver
393 | Gold
40
41id | user_id | email | pack_id | pack_name | quota
42-----------------------------------------------------------
431 | 1 | user1@mail.com | 3 | Gold | 1000
442 | 2 | user2@mail.com | 2 | Silver | 100
453 | 3 | user3@mail.com | 1 | Basic | 10