Comparison between two sql tables

Question

Comparison between two sql tables

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

Well I need help with my sql code. This is my code:

SELECT Pedidos.Numero, Pedidos.Estado, Pedidositems.Cantidad AS CantidadPedida, RemitosItems.Cantidad AS CantidadEntregada, pedidositems.Codigo AS CodigodeProducto, pedidositems.Descripción AS DescripcionProducto

FROM (((((Empresas
LEFT JOIN 'productos' ON ('productos'.'recid'='pedidositems'.'idproducto'))
INNER JOIN 'pedidositems' ON ('pedidositems'.'idpedido'='pedidos'.'recid')) 
INNER JOIN 'pedidos' ON ('pedidos'.'idref'='contactos'.'idcontacto') AND pedidos.estado = 0)
LEFT JOIN remitositems ON ('remitositems'.'idpedprod'='pedidositems'.'recid'))
LEFT JOIN remitos ON (remitos.RecID=remitositems.IDRemito) AND remitos.estado =1)

This is the result for one of the many companies

Basically what I would need, is that all those products that are already delivered completely disappear. That I keep the products as the second item, or also partially delivered products if they were in the order. I thought to put some kind of relationship between the Quantities of the orders and the Remitos, if they are different or something of that, but when I tried it I filtered the data badly.

Well that was my problem, I hope someone can help me. Thank you very much

sql consulta tablas

asked by Marco 10.08.2018 в 15:08

source

3 answers

How can I translate the incoming data from a database with thymeleaf? Mpdf. Foreach does not work, to generate a pdf of a select

score 0 · Answer 1

I see that you have not shared the structure of your tables, but based on the aliases of your select I could say that your solution is the following one; you must use a where in which you indicate that the quantity delivered is less than the one requested, thus you would exclude those that have been delivered completely:

WHERE Pedidositems.Cantidad > RemitosItems.Cantidad

Add that condition and that's it. Greetings.

score 0 · Answer 2

If I am sure, and due to the fact that without this restraint there are many orders with undelivered products, and once that restriction is applied, only the different amounts appear but not those that have 0. I show Rostan example: Without the Where:

With the Where:

As you can see those that have quantities delivered 0, they also disappear

score 0 · Answer 3

From what I understood, you only want to show those that have not yet been delivered:

SOL (with its syntax)

                SELECT Pedidos.Numero, Pedidos.Estado, Pedidositems.Cantidad AS CantidadPedida, RemitosItems.Cantidad AS CantidadEntregada, pedidositems.Codigo AS CodigodeProducto, pedidositems.Descripción AS DescripcionProducto

                FROM (((((Empresas
                LEFT JOIN 'productos' ON ('productos'.'recid'='pedidositems'.'idproducto'))
                INNER JOIN 'pedidositems' ON ('pedidositems'.'idpedido'='pedidos'.'recid')) 
                INNER JOIN 'pedidos' ON ('pedidos'.'idref'='contactos'.'idcontacto') AND pedidos.estado = 0)
                LEFT JOIN remitositems ON ('remitositems'.'idpedprod'='pedidositems'.'recid') )
                LEFT JOIN remitos ON (remitos.RecID=remitositems.IDRemito) AND remitos.estado =1) where Pedidositems.Cantidad > RemitosItems.Cantidad OR RemitosItems.Cantidad IS NULL