SQL Server: How to join this two tables? -
could me? trying join these 2 tables
table producto:
+-------------+-------------+-------------------------------------------+----------------------+----------+-------+------------+-------+------------------------+----------+-------+-------+-------------------------------------------+----------------------+------------+-------+----------+----------+--------+--------+---------------+----------+------------+------------+---------------+---------------+--------+--------+------------------+---------+---------------+--------+------------+----------+------------+----------+-------------+------------+-------------------+-------------------+ | id_sucursal | id_producto | clave_eq_sisco | num_serie | no_parte | oem | cod_barras | marca | modelo | original | linea | nuevo | descr_larga | descr_corta | existencia | costo | impuesto | utilidad | precio | oferta | precio_oferta | cantidad | f_inicio | f_fin | precio_maximo | precio_minimo | maximo | minimo | compatibilidades | estatus | observaciones | codigo | f_alta | usr_alta | f_baja | usr_baja | rendimiento | estatus_oc | porcentaje_maximo | porcentaje_minimo | +-------------+-------------+-------------------------------------------+----------------------+----------+-------+------------+-------+------------------------+----------+-------+-------+-------------------------------------------+----------------------+------------+-------+----------+----------+--------+--------+---------------+----------+------------+------------+---------------+---------------+--------+--------+------------------+---------+---------------+--------+------------+----------+------------+----------+-------------+------------+-------------------+-------------------+ | cordoba | 1219 | torcsamsan0000001219 | 28837 | | 28837 | 28837 | sams | | | torc | n | toner botella alterno 220g | | 43 | 0 | 16 | 0 | 0 | n | 0 | 0 | 12/03/2013 | 12/03/2013 | 0 | 0 | 0 | 0 | | | | | 12/03/2013 | compras | 01/01/1900 | | 5000 | | 0 | 0 | | tehuacan | 1219 | torcsamsan0000001219 | 28837 | | 28837 | 28837 | sams | | | torc | n | toner botella alterno 220g | | 31 | 0 | 16 | 0 | 0 | n | 0 | 0 | 12/03/2013 | 12/03/2013 | 0 | 0 | 0 | 0 | | | | | 12/03/2013 | compras | 01/01/1900 | | 5000 | | 0 | 0 | | xalapa | 1219 | torcsamsan0000001219 | 28837 | | 28837 | 28837 | sams | | | torc | n | toner botella alterno 220g | | 10 | 0 | 16 | 0 | 0 | n | 0 | 0 | 12/03/2013 | 12/03/2013 | 0 | 0 | 0 | 0 | | | | | 12/03/2013 | compras | 01/01/1900 | | 5000 | | 0 | 0 | | cordoba | 612 | torchepaan0000000612 | 32385 | | 32385 | 32385 | hepa | laser jet-1012 | | torc | n | toner recarga generico 100 g hp | | 1 | 0 | 16 | 0 | 0 | n | 0 | 0 | 18/12/2012 | 18/12/2012 | 0 | 0 | 0 | 0 | | | | | 18/12/2012 | sup | 01/01/1900 | | 0 | | 0 | 0 | | tehuacan | 612 | torchepaan0000000612 | 32385 | | 32385 | 32385 | hepa | laser jet-1012 | | torc | n | toner recarga generico 100 g hp | | 8 | 0 | 16 | 0 | 0 | n | 0 | 0 | 18/12/2012 | 18/12/2012 | 0 | 0 | 0 | 0 | | | | | 18/12/2012 | sup | 01/01/1900 | | 0 | | 0 | 0 | | xalapa | 612 | torchepaan0000000612 | 32385 | | 32385 | 32385 | hepa | laser jet-1012 | | torc | n | toner recarga generico 100 g hp | | 11 | 0 | 16 | 0 | 0 | n | 0 | 0 | 18/12/2012 | 18/12/2012 | 0 | 0 | 0 | 0 | | | | | 18/12/2012 | sup | 01/01/1900 | | 0 | | 0 | 0 | | cordoba | 717 | torckatuan0000000717 | 37069 | 37069 | 37069 | 37069 | shar | al-2031 | | torc | n | toner recarga generico 240 g sharp | | 8 | 0 | 16 | 0 | 0 | n | 0 | 0 | 20/12/2012 | 20/12/2012 | 0 | 0 | 0 | 0 | | | | | 20/12/2012 | sup | 01/01/1900 | | 5000 | | 0 | 0 | | tehuacan | 717 | torckatuan0000000717 | 37069 | 37069 | 37069 | 37069 | shar | al-2031 | | torc | n | toner recarga generico 240 g sharp | | 15 | 0 | 16 | 0 | 0 | n | 0 | 0 | 20/12/2012 | 20/12/2012 | 0 | 0 | 0 | 0 | | | | | 20/12/2012 | sup | 01/01/1900 | | 5000 | | 0 | 0 | | xalapa | 717 | torckatuan0000000717 | 37069 | 37069 | 37069 | 37069 | shar | al-2031 | | torc | n | toner recarga generico 240 g sharp | | 11 | 0 | 16 | 0 | 0 | n | 0 | 0 | 20/12/2012 | 20/12/2012 | 0 | 0 | 0 | 0 | | | | | 20/12/2012 | sup | 01/01/1900 | | 5000 | | 0 | 0 | | cordoba | 1023 | torckatuan0000001023 | tk-1147-172--137-162 | 44259 | 44259 | 29458 | kymi | | | torc | n | toner recarga generico 290gr. kyocera | | 19 | 0 | 16 | 0 | 0 | n | 0 | 0 | 17/01/2013 | 17/01/2013 | 0 | 0 | 0 | 0 | | | | | 17/01/2013 | sup | 01/01/1900 | | 7000 | | 0 | 0 | | tehuacan | 1023 | torckatuan0000001023 | tk-1147-172--137-162 | 44259 | 44259 | 29458 | kymi | | | torc | n | toner recarga generico 290gr. kyocera | | 27 | 0 | 16 | 0 | 0 | n | 0 | 0 | 17/01/2013 | 17/01/2013 | 0 | 0 | 0 | 0 | | | | | 17/01/2013 | sup | 01/01/1900 | | 7000 | | 0 | 0 | | xalapa | 1023 | torckatuan0000001023 | tk-1147-172--137-162 | 44259 | 44259 | 29458 | kymi | | | torc | n | toner recarga generico 290gr. kyocera | | 18 | 0 | 16 | 0 | 0 | n | 0 | 0 | 17/01/2013 | 17/01/2013 | 0 | 0 | 0 | 0 | | | | | 17/01/2013 | sup | 01/01/1900 | | 7000 | | 0 | 0 | | cordoba | 1803 | tinchepadeskjetadvantage-2515on0000001803 | # 662 | cz103al | | cz103al | hepa | deskjet advantage-2515 | o | tinc | n | cartucho tinta original hp negro # 662 | cartucho negro #662 | 0 | 0 | 16 | 0 | 0 | n | 0 | 0 | 08/02/2014 | 08/02/2014 | 0 | 0 | 0 | 0 | | | | | 08/02/2014 | compras | 01/01/1900 | | 100 | | 0 | 0 | | tehuacan | 1803 | tinchepadeskjetadvantage-2515on0000001803 | # 662 | cz103al | | cz103al | hepa | deskjet advantage-2515 | o | tinc | n | cartucho tinta original hp negro # 662 | cartucho negro #662 | 0 | 0 | 16 | 0 | 0 | n | 0 | 0 | 08/02/2014 | 08/02/2014 | 0 | 0 | 0 | 0 | | | | | 08/02/2014 | compras | 01/01/1900 | | 100 | | 0 | 0 | | xalapa | 1803 | tinchepadeskjetadvantage-2515on0000001803 | # 662 | cz103al | | cz103al | hepa | deskjet advantage-2515 | o | tinc | n | cartucho tinta original hp negro # 662 | cartucho negro #662 | 1 | 0 | 16 | 0 | 0 | n | 0 | 0 | 08/02/2014 | 08/02/2014 | 0 | 0 | 0 | 0 | | | | | 08/02/2014 | compras | 01/01/1900 | | 100 | | 0 | 0 | | cordoba | 1802 | tinchepadeskjetadvantage-2515on0000001802 | 662 | cz104al | | cz104al | hepa | deskjet advantage-2515 | o | tinc | n | cartucho tinta original hp tricolor # 662 | cartucho # 662 trico | 0 | 0 | 16 | 0 | 0 | n | 0 | 0 | 08/02/2014 | 08/02/2014 | 0 | 0 | 0 | 0 | | | | | 08/02/2014 | compras | 01/01/1900 | | 100 | | 0 | 0 | | tehuacan | 1802 | tinchepadeskjetadvantage-2515on0000001802 | 662 | cz104al | | cz104al | hepa | deskjet advantage-2515 | o | tinc | n | cartucho tinta original hp tricolor # 662 | cartucho # 662 trico | 0 | 0 | 16 | 0 | 0 | n | 0 | 0 | 08/02/2014 | 08/02/2014 | 0 | 0 | 0 | 0 | | | | | 08/02/2014 | compras | 01/01/1900 | | 100 | | 0 | 0 | | xalapa | 1802 | tinchepadeskjetadvantage-2515on0000001802 | 662 | cz104al | | cz104al | hepa | deskjet advantage-2515 | o | tinc | n | cartucho tinta original hp tricolor # 662 | cartucho # 662 trico | 1 | 0 | 16 | 0 | 0 | n | 0 | 0 | 08/02/2014 | 08/02/2014 | 0 | 0 | 0 | 0 | | | | | 08/02/2014 | compras | 01/01/1900 | | 100 | | 0 | 0 | +-------------+-------------+-------------------------------------------+----------------------+----------+-------+------------+-------+------------------------+----------+-------+-------+-------------------------------------------+----------------------+------------+-------+----------+----------+--------+--------+---------------+----------+------------+------------+---------------+---------------+--------+--------+------------------+---------+---------------+--------+------------+----------+------------+----------+-------------+------------+-------------------+-------------------+ table kardex_producto:
+-------------+---------------+-----------------+----------------------+----------+-------+----------+-------+--------+--------+------------------+------------+------------+------------------+-------------+------------------+--------------+---------------+---------+------------+-------------+------------+------------+ | id_sucursal | id_movimiento | tipo_movimiento | num_serie | no_parte | oem | cantidad | costo | codigo | oferta | ord_compra | no_factura | ord_salida | no_transferencia | no_contrato | no_servicio | aut_especial | observaciones | estatus | f_entrada | usr_entrada | f_salida | usr_salida | +-------------+---------------+-----------------+----------------------+----------+-------+----------+-------+--------+--------+------------------+------------+------------+------------------+-------------+------------------+--------------+---------------+---------+------------+-------------+------------+------------+ | xalapa | 504 | e+ | 28837 | | 28837 | 10 | 0 | | n | traspaso no. 543 | | | | | traspaso no. 543 | | | f | 10/10/2016 | tcaxalger | 01/01/1900 | | | xalapa | 504 | e+ | 32385 | | 32385 | 5 | 0 | | n | traspaso no. 543 | | | | | traspaso no. 543 | | | f | 10/10/2016 | tcaxalger | 01/01/1900 | | | xalapa | 504 | e+ | 37069 | 37069 | 37069 | 10 | 0 | | n | traspaso no. 543 | | | | | traspaso no. 543 | | | f | 10/10/2016 | tcaxalger | 01/01/1900 | | | xalapa | 504 | e+ | tk-1147-172--137-162 | 44259 | 44259 | 10 | 0 | | n | traspaso no. 543 | | | | | traspaso no. 543 | | | f | 10/10/2016 | tcaxalger | 01/01/1900 | | | xalapa | 505 | e+ | # 662 | cz103al | | 1 | 0 | | n | traspaso no. 549 | | | | | traspaso no. 549 | | | f | 13/10/2016 | tcaxalger | 01/01/1900 | | | xalapa | 505 | e+ | 662 | cz104al | | 1 | 0 | | n | traspaso no. 549 | | | | | traspaso no. 549 | | | f | 13/10/2016 | tcaxalger | 01/01/1900 | | +-------------+---------------+-----------------+----------------------+----------+-------+----------+-------+--------+--------+------------------+------------+------------+------------------+-------------+------------------+--------------+---------------+---------+------------+-------------+------------+------------+ i trying make report shows me, table kardex_producto, id_sucursal, id_movimiento, cantidad, costo, ord_compra and, instead of num_serie, or no_parte, or oem, shows descr_larga producto.
i tried statement:
select producto.descr_larga, kardex_producto.id_sucursal, kardex_producto.id_movimiento, kardex_producto.cantidad, kardex_producto.costo, kardex_producto.ord_compra kardex_producto inner join producto on kardex_producto.no_parte = producto.no_parte , kardex_producto.id_sucursal = 'xalapa' (kardex_producto.id_sucursal = 'xalapa' , kardex_producto.id_sucursal = producto.id_sucursal) , (kardex_producto.f_entrada >= convert(datetime, '20161001', 103)) or (kardex_producto.f_salida >= convert(datetime, '20161001', 103)) but returning wrong results, this:
+------------------------------------------------------+-------------+---------------+----------+-------+ | descr_larga | id_sucursal | id_movimiento | cantidad | costo | +------------------------------------------------------+-------------+---------------+----------+-------+ | toner cartucho remanofacturado | xalapa | 1102 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1104 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1122 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1125 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1125 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1133 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1146 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1146 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1147 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1134 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1138 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1141 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1142 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1144 | 1 | 0 | | toner cartucho remanofacturado | xalapa | 1145 | 2 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1102 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1104 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1122 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1125 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1125 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1133 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1146 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1146 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1147 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1134 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1138 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1141 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1142 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1144 | 1 | 0 | | toner cartucho generico equivalente tn-850 hp | xalapa | 1145 | 2 | 0 | | balero de rodillo de presion | xalapa | 1102 | 1 | 0 | | balero de rodillo de presion | xalapa | 1104 | 1 | 0 | | balero de rodillo de presion | xalapa | 1122 | 1 | 0 | | balero de rodillo de presion | xalapa | 1125 | 1 | 0 | | balero de rodillo de presion | xalapa | 1125 | 1 | 0 | | balero de rodillo de presion | xalapa | 1133 | 1 | 0 | | balero de rodillo de presion | xalapa | 1146 | 1 | 0 | | balero de rodillo de presion | xalapa | 1146 | 1 | 0 | | balero de rodillo de presion | xalapa | 1147 | 1 | 0 | | balero de rodillo de presion | xalapa | 1134 | 1 | 0 | | balero de rodillo de presion | xalapa | 1138 | 1 | 0 | | balero de rodillo de presion | xalapa | 1141 | 1 | 0 | | balero de rodillo de presion | xalapa | 1142 | 1 | 0 | | balero de rodillo de presion | xalapa | 1144 | 1 | 0 | | balero de rodillo de presion | xalapa | 1145 | 2 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1102 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1104 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1122 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1125 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1125 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1133 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1146 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1146 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1147 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1134 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1138 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1141 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1142 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1144 | 1 | 0 | | chip cartucho xerox 013r00601 | xalapa | 1145 | 2 | 0 | | lamina alterno canon ir-1600 | xalapa | 1102 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1104 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1122 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1125 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1125 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1133 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1146 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1146 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1147 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1134 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1138 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1141 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1142 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1144 | 1 | 0 | | lamina alterno canon ir-1600 | xalapa | 1145 | 2 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1102 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1104 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1122 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1125 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1125 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1133 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1146 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1146 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1147 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1134 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1138 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1141 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1142 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1144 | 1 | 0 | | toner hp recarga color negro 55 grs chip incluido | xalapa | 1145 | 2 | 0 | +------------------------------------------------------+-------------+---------------+----------+-------+ when results should like
+----------------------+-------------------------------------------+-------------+---------------+----------+--------+------------------+------------+------------+ | num_serie | descr_larga | id_sucursal | id_movimiento | cantidad | costo | ord_compra | f_entrada | f_salida | +----------------------+-------------------------------------------+-------------+---------------+----------+--------+------------------+------------+------------+ | 28837 | toner botella alterno 220g | xalapa | 504 | 10 | 398 | traspaso no. 543 | 10/10/2016 | 01/01/1900 | | 32385 | toner recarga generico 100 g hp | xalapa | 504 | 5 | 130 | traspaso no. 543 | 10/10/2016 | 01/01/1900 | | 37069 | toner recarga generico 240 g sharp | xalapa | 504 | 10 | 230 | traspaso no. 543 | 10/10/2016 | 01/01/1900 | | tk-1147-172--137-162 | toner recarga generico 290gr. kyocera | xalapa | 504 | 10 | 398 | traspaso no. 543 | 10/10/2016 | 01/01/1900 | | # 662 | cartucho tinta original hp negro # 662 | xalapa | 505 | 1 | 185.76 | traspaso no. 549 | 13/10/2016 | 01/01/1900 | | 662 | cartucho tinta original hp tricolor # 662 | xalapa | 505 | 1 | 185.76 | traspaso no. 549 | 13/10/2016 | 01/01/1900 | +----------------------+-------------------------------------------+-------------+---------------+----------+--------+------------------+------------+------------+ but giving repeated info, , don't know else try. appreciated.
thank in advance!
but kardex_producto.no_parte null won't work
need decide join on , need more data
i bet mean
select distinct producto.descr_larga, kardex_producto.id_sucursal, kardex_producto.id_movimiento, kardex_producto.cantidad, kardex_producto.costo, kardex_producto.ord_compra kardex_producto join producto on kardex_producto.no_parte = producto.no_parte , kardex_producto.id_sucursal = 'xalapa' , kardex_producto.id_sucursal = producto.id_sucursal ( kardex_producto.f_entrada >= convert(datetime, '20161001', 103) or kardex_producto.f_salida >= convert(datetime, '20161001', 103) )
Comments
Post a Comment