Join 2 SQL tables

Question

Join 2 SQL tables

Navigation

#1 by (1 votes)

0

I have these tables in sql .

Table 1

----------------------------------------------------
|   articulo   |   Ubicacion     |   Existencia    |
----------------------------------------------------
|     200116    |   cy1112       |   1             |
----------------------------------------------------
|     200116    |   ee3091       |   1             |
----------------------------------------------------

Table 2

----------------------------------------------------------------------
|   articulo   |   Ubicacion     |   Cantidad      | numero conteo    |
----------------------------------------------------------------------
|     200116    |   cc3111       |   1             |        1         |
----------------------------------------------------------------------
|     200116    |   ee3091       |   1             |        1         |
----------------------------------------------------------------------
|     200116    |   EE2102       |   1             |        1         |
----------------------------------------------------------------------
|     200116    |   DD5131       |   1             |        2         |
----------------------------------------------------------------------

Table 3

-----------------
|   efectivo    |
-----------------
|   2000        |
-----------------

The result I am trying to do is this:

Where I can join the two tables, that is, as you see the table 1 has 2 articles and the table 2 has 4 articles, and at the same time perform the following operations:

1 .- The difference is calculated ( quantity - existence where count number = 1 and ( quantity2 - existence where count number = 2 ), if you do not have quantity 2 it is left empty.
2 .- The difference in cash is calculated (difference * effective)

and lastly as this is a sample table, you can do an where per article since the tables have many more articles this is just one example.

Result:

-----------------------------------------------------------------------------------------------------------------------------------
|   articulo   |   Ubicacion     |   Existencia    |   Cantidad 1    |  Cantidad2        |  Diferencia       |    Dif efectivo    |
-----------------------------------------------------------------------------------------------------------------------------------
|     200116    |   CC3111       |   1             |        1        |                   |      0            |      0             |
-----------------------------------------------------------------------------------------------------------------------------------
|     200116    |   EE3091       |   1             |        1        |                   |      0            |      0             |
-----------------------------------------------------------------------------------------------------------------------------------
|     200116    |   DD5131       |   0             |        1        |      2            |      2            |      4000          |
-----------------------------------------------------------------------------------------------------------------------------------
|     200116    |   EE2102       |   0             |        1        |                   |      1            |      2000          |
-----------------------------------------------------------------------------------------------------------------------------------
|     200116    |   cy1112       |   1             |        0        |                   |      0            |      0             |
-----------------------------------------------------------------------------------------------------------------------------------

I hope you can help me. Thanks.

sql sql-server

asked by Eduard 25.07.2017 в 17:37

source

1 answer

How to use AJAX in a form? How many conditions can I add in a while loop?

score 1 · Accepted Answer

I think you have an error in the result since the field EXISTENCIA for the article cc3111 is 0, and therefore the DIFERENCIA and DIFEFECTIVO are different.

The SQL you are looking for is this:

SELECT *, (Diferencia * 2000) AS DifEfectivo FROM 
(
SELECT *, CASE WHEN NumeroConteo=0 THEN 0
               WHEN NumeroConteo=1 THEN cantidad1-existencia
               WHEN NumeroConteo=2 THEN cantidad2-Existencia
          END AS Diferencia
  FROM
(
SELECT Articulo, Ubicacion, SUM(Existencia) Existencia, 
  SUM(cantidad1) cantidad1 ,cantidad2, 
  SUM(NumeroConteo) AS NumeroConteo
FROM (
  SELECT ARTICULO, ubicacion, Existencia, 0 AS cantidad1, 
    0 AS cantidad2, 0 as NumeroConteo
    from tabla1
  UNION
  SELECT ARTICULO, ubicacion, 0 AS existencia, 
    cantidad AS cantidad1, IIF(NumeroConteo=2,2,0) AS cantidad2,
    NumeroConteo
    from tabla2) AS Tabla
GROUP by Articulo, Ubicacion, cantidad2
  ) AS TABLATEMP1
  ) AS TABLATEMP2

It would be necessary to recover the efectiovo of the table3, but that is already the simplest thing.

Personally I would put this inside a StoredProcedure and use cursors, to improve efficiency. The article for the filter (where) can be passed as a parameter.

The result should look something like this: