SQL Server 2008: join statement throwing incorrect results

Question

SQL Server 2008: join statement throwing incorrect results

Navigation

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

1

I'm trying to merge these two tables

Table Product :

+-------------+-------------+-------------------------------------------+----------------------+----------+-------+------------+-------+------------------------+----------+-------+-------+-------------------------------------------+----------------------+------------+-------+----------+----------+--------+--------+---------------+----------+------------+------------+---------------+---------------+--------+--------+------------------+---------+---------------+--------+------------+----------+------------+----------+-------------+------------+-------------------+-------------------+
| 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  |                        | A        | TORC  | N     | TONER BOTELLA ALTERNO 220G                |                      |         43 |     0 |       16 |        0 |      0 | N      |             0 |        0 | 12/03/2013 | 12/03/2013 |             0 |             0 |      0 |      0 |                  | A       |               |        | 12/03/2013 | COMPRAS  | 01/01/1900 |          |        5000 |            |                 0 |                 0 |
| TEHUACAN    |        1219 | TORCSAMSAN0000001219                      | 28837                |          | 28837 | 28837      | SAMS  |                        | A        | TORC  | N     | TONER BOTELLA ALTERNO 220G                |                      |         31 |     0 |       16 |        0 |      0 | N      |             0 |        0 | 12/03/2013 | 12/03/2013 |             0 |             0 |      0 |      0 |                  | A       |               |        | 12/03/2013 | COMPRAS  | 01/01/1900 |          |        5000 |            |                 0 |                 0 |
| XALAPA      |        1219 | TORCSAMSAN0000001219                      | 28837                |          | 28837 | 28837      | SAMS  |                        | A        | TORC  | N     | TONER BOTELLA ALTERNO 220G                |                      |         10 |     0 |       16 |        0 |      0 | N      |             0 |        0 | 12/03/2013 | 12/03/2013 |             0 |             0 |      0 |      0 |                  | A       |               |        | 12/03/2013 | COMPRAS  | 01/01/1900 |          |        5000 |            |                 0 |                 0 |
| CORDOBA     |         612 | TORCHEPAAN0000000612                      | 32385                |          | 32385 | 32385      | HEPA  | LASER JET-1012         | A        | 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 |                  | A       |               |        | 18/12/2012 | SUP      | 01/01/1900 |          |           0 |            |                 0 |                 0 |
| TEHUACAN    |         612 | TORCHEPAAN0000000612                      | 32385                |          | 32385 | 32385      | HEPA  | LASER JET-1012         | A        | 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 |                  | A       |               |        | 18/12/2012 | SUP      | 01/01/1900 |          |           0 |            |                 0 |                 0 |
| XALAPA      |         612 | TORCHEPAAN0000000612                      | 32385                |          | 32385 | 32385      | HEPA  | LASER JET-1012         | A        | 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 |                  | A       |               |        | 18/12/2012 | SUP      | 01/01/1900 |          |           0 |            |                 0 |                 0 |
| CORDOBA     |         717 | TORCKATUAN0000000717                      | 37069                | 37069    | 37069 | 37069      | SHAR  | AL-2031                | A        | 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 |                  | A       |               |        | 20/12/2012 | SUP      | 01/01/1900 |          |        5000 |            |                 0 |                 0 |
| TEHUACAN    |         717 | TORCKATUAN0000000717                      | 37069                | 37069    | 37069 | 37069      | SHAR  | AL-2031                | A        | 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 |                  | A       |               |        | 20/12/2012 | SUP      | 01/01/1900 |          |        5000 |            |                 0 |                 0 |
| XALAPA      |         717 | TORCKATUAN0000000717                      | 37069                | 37069    | 37069 | 37069      | SHAR  | AL-2031                | A        | 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 |                  | A       |               |        | 20/12/2012 | SUP      | 01/01/1900 |          |        5000 |            |                 0 |                 0 |
| CORDOBA     |        1023 | TORCKATUAN0000001023                      | TK-1147-172--137-162 | 44259    | 44259 | 29458      | KYMI  |                        | A        | 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 |                  | A       |               |        | 17/01/2013 | SUP      | 01/01/1900 |          |        7000 |            |                 0 |                 0 |
| TEHUACAN    |        1023 | TORCKATUAN0000001023                      | TK-1147-172--137-162 | 44259    | 44259 | 29458      | KYMI  |                        | A        | 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 |                  | A       |               |        | 17/01/2013 | SUP      | 01/01/1900 |          |        7000 |            |                 0 |                 0 |
| XALAPA      |        1023 | TORCKATUAN0000001023                      | TK-1147-172--137-162 | 44259    | 44259 | 29458      | KYMI  |                        | A        | 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 |                  | A       |               |        | 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 |                  | A       |               |        | 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 |                  | A       |               |        | 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 |                  | A       |               |        | 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 |                  | A       |               |        | 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 |                  | A       |               |        | 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 |                  | A       |               |        | 08/02/2014 | COMPRAS  | 01/01/1900 |          |         100 |            |                 0 |                 0 |
+-------------+-------------+-------------------------------------------+----------------------+----------+-------+------------+-------+------------------------+----------+-------+-------+-------------------------------------------+----------------------+------------+-------+----------+----------+--------+--------+---------------+----------+------------+------------+---------------+---------------+--------+--------+------------------+---------+---------------+--------+------------+----------+------------+----------+-------------+------------+-------------------+-------------------+

Table Kardex_Product :

+-------------+---------------+-----------------+----------------------+----------+-------+----------+-------+--------+--------+------------------+------------+------------+------------------+-------------+------------------+--------------+---------------+---------+------------+-------------+------------+------------+
    | 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 |            |
    +-------------+---------------+-----------------+----------------------+----------+-------+----------+-------+--------+--------+------------------+------------+------------+------------------+-------------+------------------+--------------+---------------+---------+------------+-------------+------------+------------+

To make a report that shows, from the table Kardex_Product, Id_Sucursal, Id_Movement, Quantity, Cost, Ord_Buy and, instead of num_serie, or No_part, or OEM, show Descr_Larga from the Product table.

I tried this sentence:

SELECT Producto.Descr_Larga, Kardex_Producto.Id_Sucursal, 
       Kardex_Producto.Id_Movimiento, Kardex_Producto.Cantidad, 
       Kardex_Producto.Costo, Kardex_Producto.Ord_Compra FROM   Kardex_Producto  JOIN   Producto 
         ON Kardex_Producto.No_Parte = Producto.No_Parte 
        and Kardex_Producto.Id_Sucursal = 'xalapa'
        and Kardex_Producto.Id_Sucursal = Producto.Id_Sucursal WHERE (   Kardex_Producto.F_Entrada >= CONVERT(DateTime, '20161001', 103) 
       OR Kardex_Producto.F_Salida  >= CONVERT(DateTime, '20161001', 103)
      )

but returns erroneous results (repeated rows) when something similar to

should return

+----------------------+-------------------------------------------+-------------+---------------+----------+--------+------------------+------------+------------+
|      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 |
+----------------------+-------------------------------------------+-------------+---------------+----------+--------+------------------+------------+------------+

actually returns repeated information

+------------------------------------------------------+-------------+---------------+----------+-------+------------------+
|                     Descr_Larga                      | Id_Sucursal | Id_Movimiento | Cantidad | Costo |    Ord_Compra    |
+------------------------------------------------------+-------------+---------------+----------+-------+------------------+
| TONER CARTUCHO REMANOFACTURADO                       | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| TONER CARTUCHO  GENERICO EQUIVALENTE A TN-850  HP    | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| BALERO DE RODILLO DE PRESION                         | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
|                                                      | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
|                                                      | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
|                                                      | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
|                                                      | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
|                                                      | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| CHIP CARTUCHO XEROX 013R00601                        | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| LAMINA ALTERNO CANON IR-1600                         | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| TONER HP RECARGA  COLOR NEGRO 55 GRS CHIP INCLUIDO   | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| TONER RECARGA HP COLOR CYAN 45GRS CHIP INCLUIDO      | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| TONER RECARGA HP COLOR MAGENTA 45 GRS CHIP INCLUIDO  | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| RECARGA TONER HP COLOR AMARILLO 45 GRS CHIP INCLUIDO | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| CHIP CARTUCHO CE321A CYAN                            | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| CHIP CARTUCHO MAGENTA CE323A                         | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| CHIP CARTUCHO CE320A                                 | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| CHIP CARTUCHO HP COLOR AMARILLO CE322A               | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| TONER RECARGA GENERICO  195G. LEXMARK                | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
| CHIP  GENERICO  LEXMARK                              | XALAPA      |           504 |       10 |   398 | Traspaso No. 543 |
+------------------------------------------------------+-------------+---------------+----------+-------+------------------+

And I do not understand why, or how to solve it ...

I thank you in advance for all your support.

sql sql-server

asked by antonio_veneroso 08.11.2016 в 20:23

source

3 answers

Error when trying to launch a second emulator Map Control Genexus

score 1 · Answer 1

It does not really work badly. Note that you are crossing null (or blank) values of the No_Part field along with the values of the Serial_ID. If there are repeated values, you will get as many repeated values as there are coincidences. That is: If you have two equal values in one field and another 3 in another, you will get 6.

If you want to make this type of crosses and return only one record you will have to look for unique values.

score 1 · Answer 2

1

There are two ways to solve this:

Add distinct : example Select distinct ...

Grouping your result

These solutions come to mind, I hope they can help you with something. Greetings.

answered by 14.12.2016 в 01:49

score -1 · Answer 3

I think you're putting a wrong condition in the join. Try this:

    SELECT Producto.Descr_Larga, Kardex_Producto.Id_Sucursal, 
         Kardex_Producto.Id_Movimiento, Kardex_Producto.Cantidad, 
         Kardex_Producto.Costo, Kardex_Producto.Ord_Compra FROM Kardex_Producto  JOIN   Producto 
     ON Kardex_Producto.No_Parte = Producto.No_Parte 
    and Kardex_Producto.Id_Sucursal = Producto.Id_Sucursal WHERE ((   Kardex_Producto.F_Entrada >= CONVERT(DateTime, '20161001', 103) 
   OR Kardex_Producto.F_Salida  >= CONVERT(DateTime, '20161001', 103)
  ) and Kardex_Producto.Id_Sucursal = 'xalapa')

It should work. The error is that you are entering a general condition within the join that is used to "pair" two or more tables. I hope you serve