Compare number of credits users using two different tables in MySql 8

[comunidadmexicanarom]

Hi,

On my MySql version 8.0.17 database I have two different tables.

In the table table_credits_used I stored for each user the number of access in shared area for current date.

mysql> SELECT
       COUNT(*) tCount,
       tNameUser
FROM
    `table_credits_used` 
WHERE
    tDate = CURDATE() 
GROUP BY
    tDate,
    tNameUser;

+--------+-----------+
| tCount | tNameUser |
+--------+-----------+
|      1 | Chenoa    |
|      6 | Kimi      |
|      1 | Aponi     |
|      1 | Imala     |
|      6 | Doba      |
|      1 | Elsu      |
|      1 | Jair      |
|      2 | Nova      |
|      1 | Aarav     |
|      1 | Aarush    |
|      1 | Aaryan    |
|      1 | Aayansh   |
|      1 | Aayush    |
|      5 | Abeer     |
|      1 | Adhrit    |
|      2 | Adi       |
|      1 | Aditya    |
|      1 | Advaith   |
|      6 | Advay     |
|      6 | Advik     |
|      6 | Agastya   |
+--------+-----------+
21 rows in set (0.04 sec)

In the table table_credit_to_use is stored for each user the maximum number of access in shared area for current date.

mysql> SELECT * FROM `table_credit_to_use`;
+--------+-----------+-----+
| tCount | tNameUser | tID |
+--------+-----------+-----+
|      1 | Chenoa    |   1 |
|      6 | Kimi      |   2 |
|      1 | Aponi     |   3 |
|      1 | Imala     |   4 |
|      6 | Doba      |   5 |
|      1 | Elsu      |   6 |
|      1 | Jair      |   7 |
|      2 | Nova      |   8 |
|      1 | Aarav     |   9 |
|      1 | Aarush    |  10 |
|      1 | Aaryan    |  11 |
|      1 | Aayansh   |  12 |
|      1 | Aayush    |  13 |
|      6 | Abeer     |  14 |
|      1 | Adhrit    |  15 |
|      2 | Adi       |  16 |
|      1 | Aditya    |  17 |
|      1 | Advaith   |  18 |
|      6 | Advay     |  19 |
|      6 | Advik     |  20 |
|      6 | Agastya   |  21 |
+--------+-----------+-----+
21 rows in set (0.04 sec)

I need to show users who have not consumed the maximum number of logins for the current date.

In this example, I need this output:

+--------+-----------+-----+
| tCount | tNameUser | tID |
+--------+-----------+-----+
|      5 | Abeer     |  14 |
+--------+-----------+-----+

Because the user Abeer has the right to 6 total accesses, but today he has used only 5.

Any suggestion?

My table structure below on db-fiddle.com , which offers MySQL 8.0

Thanks in advance for any help.

[r937]

SELECT t.tNameUser 
     , t.tCount             AS count_to_use 
     , COALESCE(u.tCount,0) AS count_already_used
     , t.tCount -
       COALESCE(u.tCount,0) AS count_left_to_use
  FROM table_credit_to_use AS t
LEFT OUTER
  JOIN ( SELECT COUNT(*) tCount
              , tNameUser
           FROM table_credits_used 
  --        WHERE tDate = CURDATE() 
         GROUP 
             BY tNameUser ) AS u  
    ON u.tNameUser = t.tNameUser
 WHERE t.tCount > COALESCE(u.tCount,0)   

your fiddle did not include the tDate column, so i commented that out