Consulta selectiva

computer_si · #1 (**permalink**) 23/04/2012, 22:32

Hola a todos, quisiera ver si alguien me puede ayudar en la siguiente consulta: tengo una tabla en MySQL con varios campos, entre ellos ref, mov, imp.

La consulta es la siguiente si existen dos registros donde ref e imp son iguales, y mov sea 1 y el otro 2, que no me los muestre

Reg ref mov imp
--------------------------------
1 "A1" "1" 500.00
2 "A2" "1" 200.00
3 "A3" "1" 300.00
4 "A4" "1" 500.00
5 "A5" "1" 4500.00
6 "A1" "2" 500.00
7 "A6" "1" 500.00
8 "A5" "2" 4500.00

En la tabla anterior solo tendria que mostrarme los registros 2,3,4,7 ya que el 1 corresponde con el 6 y el 5 corresponde con el 8.

No se si me explique,

Gracias Anticipadas. y saludos

REVIDEUX · #2 (**permalink**) 24/04/2012, 01:04

Hola que tal Computer_si, espero que esto te sirva:

select f.reg,f.ref, f.mov,f.imp from TablaProof as f
left join
(select t.reg reg1, c.reg reg2 from TablaProof as t left join
(select * from TablaProof) as c
on (t.ref = c.ref ) where (t.mov ='1' and c.mov = '2')
and (t.imp = c.imp)) as t
on f.reg = t.reg1 or f.reg = t.reg2
where t.reg1 is null and t.reg2 is null

Esto esta en SQL Server pero creo que de aqui ya puedes adaptarlo sin inconvenientes.

Suerte, hasta otra oportunidad.

computer_si · #3 (**permalink**) 24/04/2012, 08:13

Hola REVIDEUX adapte el codigo de la siguiente manera:

include 'conexion.php';
$link = conectar();

$sql="SELECT f.regId, f.ref, f.tipMov, f.imp FROM cuentas AS f LEFT JOIN (SELECT t.regId reg1, t.regId reg2, t.ref, t.tipMov, t.imp FROM cuentas AS t LEFT JOIN (SELECT * FROM cuentas) AS c ON (t.ref = c.ref ) WHERE (t.tipMov ='1' and c.tipMov = '2') AND (t.imp = c.imp)) AS t ON f.regId = t.reg1 OR f.regId = t.reg2 WHERE t.reg1 IS null and t.reg2 IS null ORDER BY f.regId ASC";

$result = mysql_query($sql, $link) or die(mysql_error());
$row = mysql_fetch_row($result);
if($row < 0){
echo "! ! No selecciono nada! !";
}else{
while($row=mysql_fetch_array($result)) {
echo $row[0].'--'.$row[1].'--'.$row[2].'--'.$row[3].'<br>';
}
}
desconectar();

los campos en la tabla

regId int(10)
ref varchar(10)
tipMov int(2)
imp int(15)

y la consulta me muestra

3--A3--1--300
4--A4--1--500
6--A1--2--500
7--A6--1--500
8--A5--2--4500

Donde puede estar la falla, saludos y gracias por responder

leonardo_josue · #4 (**permalink**) 24/04/2012, 08:39

Hola computer_si:

Creo que la consulta podría simplificarse un poco, checa este script:

Código MySQL:

Ver originalmysql> SELECT * FROM tabla;
+------+------+------+---------+
| reg  | ref  | mov  | imp     |
+------+------+------+---------+
|    1 | A1   | 1    |  500.00 |
|    2 | A2   | 1    |  200.00 |
|    3 | A3   | 1    |  300.00 |
|    4 | A4   | 1    |  500.00 |
|    5 | A5   | 1    | 4500.00 |
|    6 | A1   | 2    |  500.00 |
|    7 | A6   | 1    |  500.00 |
|    8 | A5   | 2    | 4500.00 |
+------+------+------+---------+
8 rows in set (0.00 sec)
 
 
mysql> SELECT * FROM tabla T1 WHERE
    -> (T1.mov = '1' AND NOT EXISTS (
    -> SELECT reg FROM tabla T2
    -> WHERE T1.ref = T2.ref AND T1.imp = T2.imp AND T2.mov = '2'));
+------+------+------+--------+
| reg  | ref  | mov  | imp    |
+------+------+------+--------+
|    2 | A2   | 1    | 200.00 |
|    3 | A3   | 1    | 300.00 |
|    4 | A4   | 1    | 500.00 |
|    7 | A6   | 1    | 500.00 |
+------+------+------+--------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM tabla T1 WHERE
    -> (T1.mov = '1' AND NOT EXISTS (
    -> SELECT reg FROM tabla T2
    -> WHERE T1.ref = T2.ref AND T1.imp = T2.imp AND T2.mov = '2')) OR
    -> (T1.mov = '2' AND NOT EXISTS (
    -> SELECT reg FROM tabla T2
    -> WHERE T1.ref = T2.ref AND T1.imp = T2.imp AND T2.mov = '1'));
+------+------+------+--------+
| reg  | ref  | mov  | imp    |
+------+------+------+--------+
|    2 | A2   | 1    | 200.00 |
|    3 | A3   | 1    | 300.00 |
|    4 | A4   | 1    | 500.00 |
|    7 | A6   | 1    | 500.00 |
+------+------+------+--------+
4 rows in set (0.00 sec)

La segunda consulta sería para considerar que existan registros con mov = 2 pero sin mov = 1 aunque según tu lógica de negocio no sé si esto pueda ocurrir.

Saludos
Leo

computer_si · #5 (**permalink**) 24/04/2012, 09:50

Gracias Leonardo_josue, modifique el codigo y ya funciona:

include 'conexion.php';
$link = conectar();

//$sql="SELECT f.regId, f.ref, f.tipMov, f.imp FROM cuentas AS f LEFT JOIN (SELECT t.regId reg1, t.regId reg2, t.ref, t.tipMov, t.imp FROM cuentas AS t LEFT JOIN (SELECT * FROM cuentas) AS c ON (t.ref = c.ref ) WHERE (t.tipMov ='1' and c.tipMov = '2') AND (t.imp = c.imp)) AS t ON f.regId = t.reg1 OR f.regId = t.reg2 WHERE t.reg1 IS null and t.reg2 IS null ORDER BY f.regId ASC";

$sql="SELECT * FROM cuentas T1 WHERE(T1.tipMov = '1' AND NOT EXISTS (SELECT ref, imp, tipMov FROM cuentas T2 WHERE T1.ref = T2.ref AND T1.imp = T2.imp AND T2.tipMov = '2'))";

$result = mysql_query($sql, $link) or die(mysql_error());
$row = mysql_fetch_row($result);
if($row < 0){
echo "! ! No selecciono nada! !";
}else{
echo $row[0].'--'.$row[1].'--'.$row[2].'--'.$row[3].'<br>';
while($row=mysql_fetch_array($result)) {
echo $row[0].'--'.$row[1].'--'.$row[2].'--'.$row[3].'<br>';
}
}
desconectar();

Referente a lo que comentas del tipo de negocio, tomare en cuenta la segunda consulta, pero siempre va a existir primero el tipo de movimiento "1", ahora la siguiente problematica es cuando el tipo de movimiento es dos pero el importe no corresponde esto es tomando en cuenta la tabla anterior de la siguiente manera:

+------+------+------+---------+
| reg | ref |tipMov | imp |
+------+------+------+---------+
| 1 | A1 | 1 | 500.00 |
| 2 | A2 | 1 | 200.00 |
| 3 | A3 | 1 | 300.00 |
| 4 | A4 | 1 | 500.00 |
| 5 | A5 | 1 | 4500.00 |
| 6 | A1 | 2 | 500.00 |
| 7 | A6 | 1 | 500.00 |
| 8 | A5 | 2 | 500.00 |
| 9 | A5 | 2 | 2500.00 |
| 10 | A5 | 2 | 500.00 |
+------+------+------+---------+

En este caso deberia de mostrarme igual los registros 2,3,4,7 y el 5 pero con un importe de 1000, ya que la suma de los registros 8,9,10 es igual a 3500.

Otra pregunta, sabes porque cuando entro al while, y envio el echo de los registros siempre me envia desde el segundo.

Se podra hacer esto, saludos y gracias anticipadas.

leonardo_josue · #6 (**permalink**) 24/04/2012, 11:10

Hola de nuevo computer_si:

Lo que planteas ahora es completamente distinto al problema original, ya que aquí será necesario hacer una agrupación de registros, ya que cada ref puede tener uno o varios registros con mov=2... (y supongo que será posible que también tenga varios registros con mov=1)

Si estoy en lo cierto, esto parece ser una especie de cargos y abonos, o ventas y compras, donde el resultado que esperas sea aquellas cuentas que no están saldadas... Para esto, hay muchas formas de poder abordar el problema, por ejemplo checa este script:

Código MySQL:

Ver originalmysql> SELECT * FROM tabla;
+------+------+------+---------+
| reg  | ref  | mov  | imp     |
+------+------+------+---------+
|    1 | A1   | 1    |  500.00 |
|    2 | A2   | 1    |  200.00 |
|    3 | A3   | 1    |  300.00 |
|    4 | A4   | 1    |  500.00 |
|    5 | A5   | 1    | 4500.00 |
|    6 | A1   | 2    |  500.00 |
|    7 | A6   | 1    |  500.00 |
|    8 | A5   | 2    |  500.00 |
|    9 | A5   | 2    | 2500.00 |
|   10 | A5   | 2    |  500.00 |
+------+------+------+---------+
10 rows in set (0.07 sec)
 
mysql> SELECT ref,
    -> IFNULL((SELECT SUM(T2.imp) FROM tabla T2
    -> WHERE T1.ref = T2.ref AND T2.mov = 1), 0) -
    -> IFNULL((SELECT SUM(T2.imp) FROM tabla T2
    -> WHERE T1.ref = T2.ref AND T2.mov = 2), 0) saldo
    -> FROM tabla T1 GROUP BY ref HAVING saldo != 0;
+------+---------+
| ref  | saldo   |
+------+---------+
| A2   |  200.00 |
| A3   |  300.00 |
| A4   |  500.00 |
| A5   | 1000.00 |
| A6   |  500.00 |
+------+---------+
5 rows in set (0.00 sec)

En el select se realizan dos subconsultas (una para obtener la sumatoria de los mov=1 para cada ref y otra para obtener la sumatoria de los mov=2), se realiza la resta y se muestran sólo aquellos registros que tengan un saldo distinto de 0. Esta es posible que no sea la manera más eficiente de hacer lo que quieres, pero es una de ellas.

En cuanto a la pregunta del WHILE, esta no corresponde a un tema de MySQL, por lo que debes publicarla en el foro correspondiente,

http://www.forosdelweb.com/f18/

Saludos
Leo.

computer_si · #7 (**permalink**) 24/04/2012, 11:59

Gracias nuevamente Leonardo_josue:

Todavia no pruebo el codigo, pero al igual que el anterior, debe de funcionar, en cuanto al comentario de que no es la manera mas eficiente, porque el comentario??

Desde tu punto de vista cual seria la manera mas eficiente de hacerlo, tambien te comento que estas en lo cierto en cuanto al planteamiento, lo pienso implementar en manejar cargos y abonos.

Saludos y gracias nuevamente, tambien tomo en cuenta lo del While para publicar o buscar en el foro correspndiente.

leonardo_josue · #8 (**permalink**) 24/04/2012, 12:08

Hola de nuevo:

El uso de subconsultas puede ocasionar que una consulta no sea eficiente, sobre todo cuando manejas un número importante de registros... Si fuera el caso, es posible que se tenga que utilizar alguna otra alternativa como el uso de join's, procedimientos almacenados, tablas temporales, vistas etc... Es por eso que menciono que la consulta puede no ser del todo eficiente. Si manejas un número reducido de registros entonces no deberías tener problemas, pero siempre hay que evaluar el performance de las consultas.

Saludos
Leo.

computer_si · #9 (**permalink**) 24/04/2012, 12:28

Que tal Leonardo

Referente a los joins, me imagino que quieres decir que lo maneje en tablas separadas y hacer uniones, en cuanto a las consultas almacenadas, me habian comentado que no son muy eficientes, y referente a tablas temporales, soy muy adepto a ellas, nadamas que pense que haciendo los querys directos podria ahorrarme tiempo.

Que opinion me das al respecto de lo que comento de las consultas almacenadas.

Saludos y nuevamente gracias anticipadas.

leonardo_josue · #10 (**permalink**) 24/04/2012, 12:40

Desde mi punto de vista muy particular, los procedimientos almacenados son una muy buena opción para consultas complejas... para consultas sencillas pueden resultar un esfuerzo innecesario.

Decir que una opción (consultas simples vs procedimientos almacenados) es mejor que la otra no es tan fácil de decir, depende del escenario que tengas. En lo particular siempre he optado por un híbrido es decir, utilizar procedimientos almacenados para consultas complejas o para operaciones de Insert, Delete, Update sobre tablas, ya que puedes tener un mejor control sobre quién hace que cosa... para cuestiones sencillas entonces opto por ejecutar directamente la consulta.

Independientemente de lo que elijas, revisa siempre no se afecte el rendimiento del servidor.

Saludos
Leo.

computer_si · #11 (**permalink**) 24/04/2012, 13:44

Ok Leonardo

Tomare encuenta tus puntos de vista, ya que de todo se aprende. En cuanto a que es mas rapido y eficiente, se puede hacer un query directo y con consulta almacenda y barrer la base de datos, y esto seria un indicativo para ver el mejor desempeño. es pregunta??

Saludos

computer_si · #12 (**permalink**) 24/04/2012, 16:39

Hola Leonardo

Ya cheque el query como me lo posteaste, y en efecto la ref y el saldo si me los da correctos, en donde tengo problemas es al agregar la columna de tipo en teoria me debera de salir "1", por que es el saldo de una ref A5 de tipo 1, pero me arroja el siguiente resultado:

A2|200|1
A3|300|1
A4|500|1
A5|1000|2
A6|500|1

Me imagino que el barrido es el siguiente, primero suma todo lo que encuentra con 1, en ese punto tipMov todavia es 1, despues suma lo que encuentra con 2 y en este punto tipMov ya vale 2, es por eso que tipMov siempre me sale como 2 aunque la suma de tipMov1 sea mayor que tipMov 2.

Es correcto??

Modifique de esta forma la consulta y no me funciona

"SELECT ref, IF( saldo > 0,1,2) tMov, (IFNULL((SELECT SUM(T2.imp) FROM cuentas T2 WHERE T1.ref = T2.ref AND T2.tipMov = 1), 0) - IFNULL((SELECT SUM(T2.imp) FROM cuentas T2 WHERE T1.ref = T2.ref AND T2.tipMov = 2), 0)) saldo FROM cuentas T1 GROUP BY ref HAVING saldo != 0";

Pero de esta forma la consulta si me funciona, (sera muy ineficiente de esta forma??)

"SELECT ref, IF((IFNULL((SELECT SUM(T2.imp) FROM cuentas T2 WHERE T1.ref = T2.ref AND T2.tipMov = 1), 0) - IFNULL((SELECT SUM(T2.imp) FROM cuentas T2 WHERE T1.ref = T2.ref AND T2.tipMov = 2), 0)) > 0,1,2) tMov, (IFNULL((SELECT SUM(T2.imp) FROM cuentas T2 WHERE T1.ref = T2.ref AND T2.tipMov = 1), 0) - IFNULL((SELECT SUM(T2.imp) FROM cuentas T2 WHERE T1.ref = T2.ref AND T2.tipMov = 2), 0)) saldo FROM cuentas T1 GROUP BY ref HAVING saldo != 0";

A2|1|200
A3|1|300
A4|1|500
A5|1|1000
A6|1|500

Saludos nuevamente