Consulta SQL que se me resiste otra vez!

n1x3r #1 Feb '14

Hola de nuevo, se me plantea un problemilla con una sentencia sql.
Esto es un ejemplo de las dos tablas.

TABLA_1
ID CODIGO NOMBRE
1 4 PEPE
2 5 MANUEL
3 6 LETTO

TABLA_2
ID CODIGO NOMBRE ESTADO
1 4 COSA1 0
2 4 COSA2 1
3 6 COSAX 0
4 6 COSAY 0

Lo que necesito es que se muestre el NOMBRE de la TABLA_1 en la que en la TABLA_2 todas las cosas sean 0 relacionado con el campo CODIGO.

Digamos que por ejemplo PEPE no se mostraría ya que en la tabla 2 la COSA2 esta a "1", pero el nombre de LETTO Si se mostraría ya que tanto la COSAX como la COSAY estan a "0"

No se si me he explicado con total claridad, necesito de que me ilumines.

Gracias por todo!

COSMOS #2 Feb '14

el primer paso sería que tuvieras tu clara la idea, de la forma en que te has explicado... xDD
yo te he entendido que para un ID determinado de la tabla 1, se mostrará su nombre en la query siempre y cuando para todo registro de la tabla 2 tal que tabla1.codigo = tabla2.codigo tengamos tabla2.estado = 0.
en el momento en que exista un registro cuyo tabla2.estado = 1, no se muestra(es decir, querrás devolver NULL)

dime si es asi y te saco la query o te indico como sacarla (como prefieras)

1 respuesta

n1x3r #3 Feb '14

#2 jaja, es que no es fácil de explicar, voy a intentarlo.

Que se muestre el nombre de la tabla 1 siempre y cuando los productos de la tabla 2 con el mismo código que la tabla 1 este siempre a 0 , si alguno esta a 1 ya no se puede mostrar el nombre.

olvídate de las ID esque se ha espachurrado todo.

TABLA_1
CODIGO NOMBRE
4 PEPE
5 MANUEL
6 LETTO

TABLA_2
CODIGO NOMBRE ESTADO
4 COSA1 0
4 COSA2 1
6 COSAX 0
6 COSAY 0

Para que se pueda mostar, todos los productos vinculados a ese nombre tienen que tener estado 0, si alguno tiene estado 1 no se muestra.

Para que me entendáis, la consulta tiene que funcionar como una puerta NOR

COSMOS #4 Feb '14

a ver q te parece esto, te pongo lo q se me ha ocurrido y luego el codigo
probablemente haya mil formas, cuenta aquellos codigos cuyo estado sea 1, si son un o mas, no pones nombre, si no hay, lo pones

select t1.codigo , case when cuenta = 0 then t1.nombre else null end as patatin patatan

from tabla1 t1,( select t2.codigo, count(*) as cuenta from tabla2 t2 where t2.estado = 1 group by 1)
where t1.codigo = t2.codigo

edit: también podrías hacer un exists y not exists estado = 1 y un union de las dos querys
edit2: #5 a eso me refería

vasag0 #5 Feb '14

select a.nombre from tabla_1 a where not exists (select 1 from tabla_2 where codigo = a.codigo and estado = 1);

en tu ejemplo

a.nombre

MANUEL
LETTO

1 1 respuesta

n1x3r #6 Dic '15

#5 Perdón por volver a abrir este hilo, pero he estado usando la sentencia de #5 durante este tiempo y me ha venido bien, pues eran pocas consultas, el problema es que cada vez tengo tablas mas grandes y las consultas se demoran bastante, ¿no existe otra forma de hacerlo que no sea haciendo una subconsulta?
Simplemente como bien dije en #1 cuando algunas de las cosas sea 1 que muestre uno, y si todas son 0 que muestre 0 en relaccion conla tabla 1 . Es como si fuese una puerta lógica OR para que nos entendamos.
Cuando trabajas con SQL he visto una función que me valdría pero no esta implementada en MySQL, es esta "!>" que significa no mayor que, por lo que podría decir:
Que las cosas de LEtto no sea mayor que 1
SELECT * FROM TABLA_1 as T1, TABLA_2 as T2 WHERE T1.id = T2.id AND T2.ESTADO !> 0
Pero esa consulta en MySQL no funciona.

Necesito algo de magia!

Un saludo & THX!

2 respuestas

Kiroushi #7 Dic '15

#6

SELECT * FROM tabla_1 JOIN tabla_2 ON (tabla_1.codigo = tabla_2.codigo) GROUP BY tabla_1.codigo HAVING max(tabla_2.estado) = 0

1 1 respuesta

garlor #8 Dic '15

"Para que se pueda mostar, todos los productos vinculados a ese nombre tienen que tener estado 0"

eso significa que no se muestra ningun nombre si tiene algun producto a 1?, si es asi creo que necesitaras hacer dos consultas anidadas ( no se si se llamaba asi )

en muchos lugares donde se hacen consultas en bases de datos grandes al final con el tiempo hay algunas que hay que terminar haciendolas a la 1 de la noche y que termine cuando termine, no es algo precisamente raro

1 respuesta

vasag0 #9 Dic '15

#6 Me alegra que te funcionase. Además del código que ya te han puesto, si es MySQL creo que puede darte algo más de rendimiento con un not in, es cuestión de probar o ver el plan de ejecución, porque depende de indices y cómo tengas definidas las tablas:

select a.nombre from tabla_1 a where a.codigo not in (select codigo from tabla_2 where estado = 1);

Lo que propones ahora es un poco diferente, ¿no? igual no te he entendido bien. Lo que yo entiendo es que buscas el estado más alto:

select nombre, max(estado) from tabla_1 group by nombre;

Si tiene alguno con 1, saldrá 1, si todos son 0, pues eso.

2 respuestas

Kiroushi #10 Dic '15

#9 Busca hacer un join pero que sólo salgan los que tienen todos los relacionados con estado a 0.

Creo que la forma más eficiente es la que yo le he dado. No tiene subconsultas.

Habría que mirar el explain con sus tablas y crear los índices correspondientes, pero bueno.

n1x3r #11 Dic '15

#8 Exacto, cuando alguno de las "COSAS" sea 1 no se muestre. (Me veo haciéndolas a las 1 de la noche, que igual para las 8 están terminadas. )

#9 no es el valor mas alto, en realidad es para mostrar artículos que no tengan stock en los diferentes almacenes. Me explico, tenemos unos 3 almacenes, con mismos productos pero diferente stock. Entonces cuando el producto este con Stock activo en web pero no tenga stock en ninguno de los almacenes, que se muestre.

garlor #12 Dic '15

y lo que haceis es atacar a BBDD locales o remotas?, porque el problema de la lentitud puede estar ahi, hay una manera de hacer que la consulta se ejecute en el equipo donde se encuentra la base de datos y solo te mande el resultado,
sino se hace asi basicamente lo que sucede es que manda la parte de la base de datos a filtrar al equipo que hace la consulta y este hace el filtrado, cosa que es notablemente lenta segun la configuracion de la red

1 respuesta

n1x3r #13 Dic '15

#12 Es local, estaba exagerando, la consulta toma unos 20 segundos aprox. pero se hace incomoda cuando trabajas constantemente con ella.

Esta es la sentencia que estoy usando ahora:

$xcrud->where('web.EAN != "" AND web.Stock > 0 AND web.Estado = 1 AND NOT EXISTS (SELECT id FROM master WHERE web.EAN = master.EAN AND master.Stock > 0)');

Y ese subselect me esta matando el servidor.
La tabla master es la de los almacenes.

2 respuestas

COSMOS #14 Dic '15

#13 Necesitas meter índices a esa tabla, vas a tener que recorrerla por código sí o sí.
O bien almacenar en una tabla el status de ese código y tenerla actualizada según las reglas que sigas.

Edit:

SELECT w.EAN,
FROM web w
JOIN almacen_status a
ON w.EAN = a.EAN
WHERE a.stock = 0

Se entiende que en almacen_status cada vez que el codigo se queda sin stock lo pones a 0, y cada vez que se rellena cada uno de los otros almacenes (he entendido bien? tenias varios?) le añades X. Cuando en uno de los almacenes disminuye el stock, disminuyes en la misma cantidad el stock en almacen_status. Almacen_status no dejaría de ser el total del stock en todos los almacenes, pero funcionaría xD

1 respuesta

Kiroushi #15 Dic '15

#13 ¿Has probado la que te he puesto arriba?

1 respuesta

n1x3r #16 Dic '15

#14 Según lo que pones, cuando en uno de los almacenes este a 0 se mostrara y eso no quiero, tiene que darse la circunstancia de que en todos los almacenes esten a 0 para que se muestre.

Ejemplo:

========================
tabla_productos

EAN(id)---------nombre--------stock

005-------------cable------------1
006-------------tornillo----------1
007-------------tuerca-----------1
008-------------led---------------0

=================================
tabla_alamacenes

EAN(id)--------nombre-------almacen-------stock

005------------cable----------A1-------------0
005------------cable----------A2-------------0
006------------tornillo--------A1-------------1
006------------tornillo--------A2-------------0
007------------tuerca---------A1-------------1
007------------tuerca---------A2-------------1
008------------led-------------A1-------------0
008------------led-------------A2-------------0
005------------cable----------A3-------------0
006------------tornillo--------A3-------------1
007------------tuerca---------A3-------------1

tabla_productos.Stock > 0 AND NOT EXISTS (SELECT id FROM tabla_alamacenes WHERE tabla_productos.EAN = tabla_almacenes.EAN AND tabla_almacenes.Stock > 0)

Solo se muestran productos que estén agotados en todos los almacenes pero que yo si tenga con stock 1 en tabla productos, por lo que solo se mostraría el producto "cable" tornillo y tuerca si tienen stock y led no se muestra ya que esta agotado en tabla productos y en tabla almacenes.

Espero que asi quede mas claro.

Necesito una sentencia que sólo muestre el producto "cable" y que sea lo mas optimizada posible. Pensad que el numero de almacenes puede variar, yo también podría poner que en almacén A1, A2 A3 sea 0, pero algunos productos tienen almacen A1 A2 y A3, otros solo A1 y A2 por lo que el almacén no es un valor fijo.

Para los que le interese esta es la librería que uso http://xcrud.com/documentation/index.html a mi me ha solucionado mucho la vida, y lo que cuesta (12$) es algo mínimo para lo que te facilita la vida.

1 respuesta

n1x3r #17 Dic '15

#15 No puedo hacer un groupby uso la libreria Xcrud y no me permite hacer sentencias completas, tengo que limitarme a sus funciones preestablecidas y clausulas WHERE

1 respuesta

COSMOS #18 Dic '15

#16
La mantienes cada vez que cambie un stock (en el trigger mismamente lanzas los updates)
tabla_status_EAN
EAN(pk)------stock_total_almacenes
005 ----------------0
006-----------------2
007-----------------3
008-----------------0

y haces

SELECT tp.nombre
FROM tabla_productos tp
JOIN tabla_status_EAN ts
ON ts.EAN = tp.EAN
WHERE ts.stock_total_almacenes = 0 AND tp.stock > 0

asi te vale? y los índices, importante

Kiroushi #19 Dic '15

#17 http://xcrud.com/documentation/index.html#!/select_data_custom_sql

query( sql_query ) - custom sql query. This method allow you to use custom sql query and display read-only datagrid.


$xcrud = Xcrud::get_instance();
$xcrud->query('SELECT * FROM users WHERE age > 25');
echo $xcrud->render();

2 respuestas

n1x3r #20 Dic '15

#19 Necesito usar tablas enlazadas y mostrar mas datos, no me vale sólo mostrar los datos.

1 respuesta

Kiroushi #21 Dic '15

#20 Entonces tu problema está en que te estás agarrando al uso de una librería para el procesamiento de SQL.

Si quieres depender de un ORM, hazte a la idea de que tus querys no van a ser eficientes.

¿Cuántos registros tiene tu BDD para tardar 20 segundos en esa consulta?

1 respuesta

n1x3r #22 Dic '15

#21 Productos unos 2000 y la tabla de almacén unos 60.000

eXtreM3 #23 Dic '15

Es imposible que tarde 20 segundos con ese número de registros.

¿No puedes hacer GROUP BY con esa librería y la sigues usando?

n1x3r #24 Dic '15

#7 Con tu sentencia y la forma que muestras en #19 se reduce la consulta a 2.32 segundos. de momento la voy a dejar así, y dejare de usar la subconsulta, aunque me quede con los datos básicos.

La cuestión es ver como puedo construir ese GROUP BY y ese HAVING con la librería.

2 respuestas

eXtreM3 #25 Dic '15

#24 puedes hacer consultas personalizadas utilizando el ->query directamente.

Kiroushi #26 Dic '15

#24 Crea una vista en la base de datos con esa consulta, y usa el xcrud ese sobre la vista.

Consulta SQL que se me resiste otra vez!

a.nombre

EAN(id)---------nombre--------stock

EAN(id)--------nombre-------almacen-------stock

Usuarios habituales

Tags