¿Cómo puedo hacer la siguiente consulta en SQL?

Question

Verán, necesito obtener una tabla que resulta de combinar datos de otras tres tablas, bajo las siguientes condiciones:

TABLA A OBTENER:

ITEM....CÓDIGO.........COLOR...TOTAL

CDE..........110-235...........ROJO...........100

CDE..........310-578...........VERDE.........300

CDE..........145-140...........ROJO...........400

..........

Como se observa la columna CÓDIGO está compuesta por tres dígitos que empiezan con 110, 210, 310..., 910 o con tres dígitos cualquiera y todos los códigos terminan con otros tres dígitos cualquiera, tomando todos la forma: XYZ-UVW

.........

Las columnas ITEM, CÓDIGO y COLOR se deben obtener de la TABLA1. Ingreso un ítem, por ejemplo "CDE" y debe obtener de esa columna, para dicho ítem, los valores de código y color (CÓDIGO en TABLA1 es único por cada ITEM, no se repite):

TABLA1

ITEM.......CÓDIGO.......COLOR

CDE..........110-235.....ROJO

CDE..........310-578.....VERDE

CDE..........145-140.....ROJO

ABC..........110-235.....ROJO

ABC..........812-146.....AZUL

ABC..........310-578.....VERDE

ABC..........145-140.....ROJO

.......

La columna TOTAL se obtiene de las TABLAS 2 y 3 de acuerdo al código. Así:

..............................................

Todos los códigos que empiezan con 110, 210, 310...., hasta 910 se encuentran en la TABLA2:

TABLA2

CODIGO........TOTAL

110-235.......30

110-235......70

110-236......85

210-222......114

310-578......100

310-578.....200

310-579....125

Se agrupan y totalizan todos los códigos iguales, como los resaltados en negrita. Por ejemplo se obtiene el código 110-235 y su total que es 100 (30+70=100).

...........................................

...el resto de códigos (los que no empiezan con 110, 210, 310...,910) se encuentran en la TABLA3 que tiene la misma estructura que TABLA2:

TABLA3

CÓDIGO........TOTAL

145-140........300

145-140........100

145-138........115

145-138........217

La columna TOTAL se obtiene igual que en el caso de la TABLA2: se agrupa por código y se totaliza. Así por ejemplo, para el código 145-140 (resaltado en negrita), su total es 400 (300+100=400).

Answer 1

Utiliza un select con alias.

ejemplo: SELECT Tbl1.ITEM, Tbl1.CODIGO, Tbl1.COLOR, Tbl3.TOTAL FROM Tabla1 as Tbl1, Tabla2 as  Tbl2 , Tabla3 as Tbl3

con respecto a esto:

Todos los códigos que empiezan con 110, 210, 310...., hasta 910 se encuentran en la TABLA2:

podrias utilizar un case when en el campo CODIGO para traer el dato correspondiente de la tabla segun la numeracion.

Answer 2

Hola James2016-2:

Tú escenario, según lo has descrito.

create table tabla1 (item varchar(10), codigo varchar(10), color varchar(10))
go
insert into tabla1 (item, codigo, color)
values
('CDE','110-235','ROJO'),
('CDE','310-578','VERDE'),
('CDE','145-140','ROJO'),
('ABC','110-235','ROJO'),
('ABC','812-146','AZUL'),
('ABC','310-578','VERDE'),
('ABC','145-140','ROJO');
go
create table tabla2(codigo varchar(10), total int)
go
insert into tabla2(codigo, total)
values
('110-235','30'),
('110-235','70'),
('110-236','85'),
('210-222','114'),
('310-578','100'),
('310-578','200'),
('310-579','125');
go
create table tabla3(codigo varchar(10), total int)
go
insert into tabla3(codigo, total)
values
('145-140','300'),
('145-140','100'),
('145-138','115'),
('145-138','217');
go

Una posible solución.

WITH cte
	AS (SELECT t.item
		    , t.codigo
		    , t.color
		    , t2.codigo AS codigo2
		    , t2.total AS total2
		    , t3.codigo AS codigo3
		    , t3.total AS total3
	    FROM   
		    tabla1 t
			    LEFT JOIN tabla2 t2 ON t.codigo = t2.codigo
			    LEFT JOIN tabla3 t3 ON t.codigo = t3.codigo),
	valores
	AS (SELECT c.codigo
		    , c.color
		    , c.item
		    , COALESCE(c.total2, c.total3, 0) AS total
	    FROM   
		    cte c)
	SELECT v.item
		, v.codigo
		, v.color
		, SUM(v.total) AS Total
	FROM   
		valores v
	GROUP BY v.item
		  , v.codigo
		  , v.color;

Defines una tabla de expresión común, y la relacionas con la tabla 2 y con la tabla 3, en la salida del primer conjunto recoges el campo total, utilizando coalesce, que te devolverá el primer elemento que no sea nulo.

y en la salida de este conjunto, haces una suma agrupada.

Se puede ahorrar un paso sin dificultad, pero creo que de este modo es más didáctico.

Salida

item       codigo     color      Total
---------- ---------- ---------- -----------
ABC        110-235    ROJO       100
ABC        145-140    ROJO       400
ABC        310-578    VERDE     300
ABC        812-146    AZUL       0
CDE        110-235    ROJO       100
CDE        145-140    ROJO       400
CDE        310-578    VERDE     300

(7 filas afectadas)

Como no están ordenados los datos, a la salida del conjunto valores, pueden no encajar con lo que tu deseas. Sólo tendrías que utilizar order by y las columnas por las que ordenar.

Tabla de expresión común

https://javifer2.blogspot.com/search/label/tablas%20de%20expresión%20común%20%281%29

Coalesce

https://javifer2.blogspot.com/search/label/coalesce

Answer 3

Gracias, está bastante didáctico.

Se me fue ese dato, efectivamente las columnas están ordenadas, las tablas tienen índices definidos en base al código. En el caso de Tabla1, Código es una llave primaria (PK) y en el caso de las otras tablas (2 y 3) son índices.

Algunas consultas: Como las tablas ya tienen índices definidos, durante el proceso, el motor de base de datos, toma automáticamente los datos ya ordenados ¿noo?

Otra, ¿si no entendí mal, esa tabla de expresión común se crea nada más en memoria?

El resultado final está bien, sólo que no deseo obtener todos los ítems, sino uno específico que ingrese, por ejemplo, si ingreso "ABC" que traiga las filas con ABC y si ingreso  "CDE" que traiga sólo las filas con CDE. ¿Cómo le especificaría ese parámetro?

Y una última cosita, para ponerlo en código de VB Net como una cadena de consulta SQL, basta con entrecomillar el código anterior? (claro, sin incluir los datos de prueba) o ¿habría que hacer algo más?

Answer 4

Hola James2016-2:

Ordenación: No. En Sql no existe forma de saber el orden de los datos, salvo que utilices order by. Suele ocurrir lo que comentas, pero no funciona así. El motor, obtiene los datos, en función de lo que le resulte más rápido, por tanto la única manera de predecir la ordenación es utilizando order by.

Tabla de expresión, memoria=Si.

Parametro: Por ejemplo

tabla1 t
			    LEFT JOIN tabla2 t2 ON t.codigo = t2.codigo
			    LEFT JOIN tabla3 t3 ON t.codigo = t3.codigo
Where t.item = @elParametro),

Vb.Net: Explicito como lo has dicho.

Answer 5

Muchas gracias.

Answer 6

Una molestia más, ahora que estaba haciendo pruebas me di cuenta que se me pasó un gran detalle y es que las tablas 2 y 3 tienen también la columna ITEM:

create table tabla1 (item varchar(10), codigo varchar(10), color varchar(10))
go
insert into tabla1 (item, codigo, color)
values
('CDE','110-236','ROJO'),
('CDE','110-235','ROJO'),
('CDE','310-578','VERDE'),
('CDE','310-579','VERDE'),
('CDE','210-222','AZUL'),
('CDE','145-140','ROJO'),
('ABC','110-235','ROJO'),
('ABC','110-236','ROJO'),
('ABC','812-146','AZUL'),
('ABC','310-578','VERDE'),
('ABC','145-140','ROJO');
('ABC','','ROJO');
('ABC','','AZUL');
go
create table tabla2(item varchar(10), codigo varchar(10), total int)
go
insert into tabla2(item, codigo, total)
values
('ABC','110-235','80'),
('ABC','110-235','50'),
('ABC','110-236','85'),
('ABC','310-578','50'),
('ABC','310-578','70'),
('CDE','110-236','45'),
('CDE','210-222','114'),
('CDE','310-578','100'),
('CDE','310-578','200'),
('CDE','310-579','125');
go
create table tabla3(item varchar(10), codigo varchar(10), total int)
go
insert into tabla3(item, codigo, total)
values
('ABC','145-140','300'),
('ABC','145-140','100'),
('ABC','812-146','105'),
('ABC','812-146','25'),
('CDE','145-140','75'),
('CDE','145-138','115'),
('CDE','145-138','217');
go

Así puede darse el caso que las tablas 2 y 3 tengan un código coincidente pero con diferente ITEM, entonces esas filas con distinto ITEM no se deben considerar para totalizar las sumas.

Por ejemplo:

('ABC','110-236','85'),
('CDE','110-236','45'),

Si ingreso como parámetro 'CDE', la tabla 2 tiene las dos filas anteriores, debe considerar la segunda fila y las otras que coinciden con el ITEM 'CDE' y ya no la primera fila porque tiene el ITEM 'ABC' (aún cuando ambas comparten el mismo código).

-----------------------------------------

También puede darse el caso que la tabla 1 tenga filas que no tienen un código (como en las dos últimas filas añadidas) y por tanto no están en las tablas 2 y 3, pero deben considerarse para añadirse a las filas resultantes y su valor sería 0, creo que ese caso es como si tuvieran un código que no existe en las otras dos tablas, esa parte me parece que sí funciona (pero lo menciono por si acaso).

-------------------------------------

- En cuanto al orden añadí al final By Order v.item 

No sé si eso será suficiente para prevenir que las filas se adquieran correctamente, de no ser así, ¿cómo sería?

¿Cómo sería la nueva consulta?