Adrian Adrian - 2 months ago 8
MySQL Question

SUM values from table 1 with ENUM condition from table 2

I have this query

$my_taxes = mysql_fetch_array(mysql_query("SELECT SUM(FEE) FROM clients WHERE EMAIL = '$EMAIL'"));


Also, I have a secondary table called payments with a column (PAID) with 2 ENUM options (pending and ready)

I need to get a variable that sum total fees by a specific email from table clients but only if the PAID column value from the secondary table is selected as pending.

This what I've tried untill now

$my_taxes = mysql_fetch_array(mysql_query("SELECT SUM(FEE) FROM clients WHERE EMAIL = '$EMAIL' UNION ALL SELECT PAID FROM payments WHERE EMAIL = '$EMAIL' AND PAID = 'pending' "));


Thank you in advance!




UPDATE 2

TABLE 1 payments
TABLE 2 clients

OUTPUT 50 | EXPECTED 25

Answer

Try query

SELECT SUM(FEE) FROM clients
       INNER JOIN payments ON payments.ID = clients.SID AND PAID = 'pending'
       WHERE clients.EMAIL = '$EMAIL'