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!
2
Answers
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.
If I understood correctly your question you want to select all the id_licensia who are not in t_licencia.
If you need to display only the license key who aren’t in t_asignation then :
Then for the sum :