Consulta selectiva

[leonardo_josue]

Hola computer_si:

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

Código MySQL:

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