How to return values in a query only if the subtraction of two queries are greater than 0 in MySQL

Question

I know, is duplicated, but I do not know how to do it without a single query. Here is my problem:

Description of the model: The "t_factura_detalle" stores the details of an invoce. The posible products for the invoce details are stored in "t_licencia". The table "t_asignacion" stores the product of a invoice (for the moment, is not important to save the invoice id from which each product is assigned) than has be assigned to a pc "id_pc" (only one license for record in the table assign).

Process in which I need help: When i do the assignation process, i need return a list in which i return "t_licencia.id_licencia" and "t_licencia.licencia_name", but i need to sustract all the licenses already asigned stored in the "t_asignation" table. An example:

"t_licencia":

+-------------+--------------------------+
| id_licencia | licencia_name            |
+-------------+--------------------------+
|           6 | Adobe Photoshop CS6 Pro  |
|           4 | Microsoft Office 2013 SP |
+-------------+--------------------------+

"t_factura_detalle":

+------------+---------------------+-------------+----------+
| id_factura | id_factura_licencia | id_licencia | cantidad |
+------------+---------------------+-------------+----------+
|          6 |                   1 |           6 |       30 |
|          6 |                   3 |           4 |       40 |
|          7 |                   4 |           4 |       40 |
|          6 |                   6 |           6 |       40 |
|          6 |                   8 |           6 |       40 |
+------------+---------------------+-------------+----------+

So, the quantity of products (licenses) are:

+--------------------------+---------------+
| licencia_name            | sum(cantidad) |
+--------------------------+---------------+
| Adobe Photoshop CS6 Pro  |           110 |
| Microsoft Office 2013 SP |            80 |
+--------------------------+---------------+

# At the moment I do not care what invoice is associated, 
# I just want to know the amount
select t_licencia.licencia_name, sum(cantidad)
from t_licencia, t_factura_detalle
where t_licencia.id_licencia = t_factura_detalle.id_licencia
group by licencia_name;

And a example of the "t_asignation":

+---------------+---------------------+-------+------------------+
| id_asignacion | id_factura_licencia | id_pc | fecha_asignacion |
+---------------+---------------------+-------+------------------+
|             2 |                   3 |     1 | 2017-00-00       |
+---------------+---------------------+-------+------------------+

So, the "t_asignacion" points to an "t_facture_detalle" record, where are a product.

How can i return values only if the the {substraction of the [sum of the available licenses] and the [sum of assigned licenses]} is greater than 0 in a stored procedure or a simply query (preferably)?

I do not know if I explain well. Thank so much!

Answer 1

Finally i managed to do it in a query. It's not so pretty, but it works... I put it here in case can help to others with similar problems. Thanks a lot to Raymond and Jean.

select totales_factura_detalle.id_factura_licencia, tl2.licencia_name
from (
    select ta.id_factura_licencia, 
        count(tl.licencia_name) as usadas, 
        (select tfd2.cantidad
    from t_factura_detalle tfd2
    where tfd2.id_factura_licencia = ta.id_factura_licencia
        ) as disponibles
    from t_asignacion ta, t_factura_detalle tfd, t_licencia tl
    where ta.id_factura_licencia = tfd.id_factura_licencia
        and tl.id_licencia = tfd.id_licencia
    group by id_factura_licencia
) totales_factura_detalle, t_factura_detalle tfd3, t_licencia tl2
where disponibles > usadas 
    and tfd3.id_factura_licencia = totales_factura_detalle.id_factura_licencia
    and tl2.id_licencia = tfd3.id_licencia;

Answer 2

If I understood correctly your question you want to select all the id_licensia who are not in t_licencia.

SELECT  id_licencia,
    licencia_name
FROM t_licencia t_l
LEFT JOIN t_factura_detalle tfd ON tfd.id_licencia = t_l.id_licencia
WHERE tfd.id_licencia IS NULL

If you need to display only the license key who aren't in t_asignation then :

SELECT  t_l.id_licencia,
        t_l.licencia_name
    FROM t_licencia t_l
    LEFT JOIN t_factura_detalle tfd ON tfd.id_licencia = t_l.id_licencia
    LEFT JOIN t_asignation t_a ON t_a.id_factura_licencia = tfd.id_factura
    WHERE t_a.id_factura_licencia IS NULL

Then for the sum :

SELECT  t_l.id_licencia,
        t_l.licencia_name,
        SUM(cantidad)
FROM t_licencia t_l
LEFT JOIN t_factura_detalle tfd ON tfd.id_licencia = t_l.id_licencia
LEFT JOIN t_asignation t_a ON t_a.id_factura_licencia = tfd.id_factura
WHERE t_a.id_factura_licencia IS NULL
GROUP BY _l.id_licencia,
        t_l.licencia_name