[SOLUCIONADO] es posible usar subconsulta en lugar de UNION

[marenas4001]

Gracias de antemano por sus consejos.
Nesito crear una consulta que genere un estado de cuenta para clientes,
una linea seran los datos de la factura y la/las siguientes con los pagos aplicados a esas mismas facturas.
Estoy tratando de filtrar por el campo entity y no hace el filtro.
obvio que esoy generando mal la consulta.
Es posible utilizar subconsultas en lugar de esta UNION

Código SQL:

SELECT
f.rowid AS rowid,
f.datef AS datef,
    CASE f.TYPE
        WHEN 0 THEN
            'Factura'
        WHEN 2 THEN
            'Credito'
        ELSE
            'Desconocido'
        END
     AS Tipo_Doc,
f.rowid AS facid,
f.facnumber AS facnumber,
f.fk_soc AS fk_soc,
s.nom AS nom,
IF ((   f.total_ttc >= 1    ),f.total_ttc,'') AS Cargo,
IF ((   f.total_ttc <= 1    ),f.total_ttc,'') AS Abono,
 s.entity AS entity
FROM llx_facture AS f   JOIN llx_societe AS s ON f.fk_soc = s.rowid
WHERE   f.fk_statut BETWEEN 1   AND 2
 
UNION ALL
 
SELECT
p.rowid AS rowid,
p.datep AS datep,
'PAGO' AS Tipo_Doc,
pf.fk_facture AS facid,
f.facnumber AS facnumber,
f.fk_soc AS fk_soc,
s.nom AS nom,
IF ((pf.amount <= 1),pf.amount,'') AS Cargo,
IF ((pf.amount >= 1),pf.amount,'') AS Abono,
s.entity AS entity
FROM llx_paiement AS p JOIN llx_paiement_facture AS pf ON p.rowid = pf.fk_paiement
JOIN llx_facture AS f ON pf.fk_facture = f.rowid
JOIN llx_societe AS s ON f.fk_soc = s.rowid
WHERE s.entity = 21
ORDER BY facid
 

Anexo fragmento del resultado separado por comas:

rowid,datef,Tipo_Doc,facid,facnumber,fk_soc,nom,Ca rgo,Cargo,entity
75,2/2/2015 00:00:00,Factura,75,FAC-00001,44,ORGANIZACIÓN AURORA S.A DE C.V,1900.00000000,,18
76,2/2/2015 00:00:00,Factura,76,FAC-00002,44,ORGANIZACIÓN AURORA S.A DE C.V,750.00000000,,18
80,2/2/2015 00:00:00,Credito,80,NCR-00001,44,ORGANIZACIÓN AURORA S.A DE C.V,,-1900.00000000,18
81,2/2/2015 00:00:00,Credito,81,NCR-00002,44,ORGANIZACIÓN AURORA S.A DE C.V,,-750.00000000,18
90,2/2/2015 00:00:00,Factura,90,FAC-00004,43,VERONICA SANTOS JOACHIN,500001.25000000,,18
92,2/2/2015 00:00:00,Factura,92,FAC-00005,43,VERONICA SANTOS JOACHIN,590000.18000000,,18
39,20/2/2015 12:00:00,PAGO,192,FAC-00035,106,PROMOTORA HOTELERA ORIGINAL SA DE CV,,638.12000000,21
192,16/2/2015 00:00:00,Factura,192,FAC-00035,106,PROMOTORA HOTELERA ORIGINAL SA DE CV,638.12000000,,21
28,19/2/2015 12:00:00,PAGO,193,FAC-00036,100,GRUPO MAJOSA SA DE CV,,939.60000000,21
193,17/2/2015 00:00:00,Factura,193,FAC-00036,100,GRUPO MAJOSA SA DE CV,939.60000000,,21
194,11/2/2015 00:00:00,Factura,194,FAC-01961,19,GRUPO INDUSTRIAL CREYSI, S.A. DE C.V.,31237.18000000,,17
196,11/2/2015 00:00:00,Factura,196,FAC-01963,19,GRUPO INDUSTRIAL CREYSI, S.A. DE C.V.,35253.79000000,,17
198,12/2/2015 00:00:00,Factura,198,FAC-01964,19,GRUPO INDUSTRIAL CREYSI, S.A. DE C.V.,63336.00000000,,17
199,12/2/2015 00:00:00,Factura,199,FAC-01965,19,GRUPO INDUSTRIAL CREYSI, S.A. DE C.V.,8184.96000000,,17

[gnzsoloyo]

Es posible utilizar subconsultas en lugar de esta UNION

¿Tienes claro el uso de UNION y las subconsultas?
Son cosas total y absolutamente diferentes.

El UNION encadena un resultado de una consulta al resultaod d ela otra, pero tienen como regla que deben tener ambas la misma cantidad de columnas, del mismo tipo y en el mismo orden.
Extensivamente, se dice que las columnas de todas las consutlas unidas deben poseer el mismo dominio, por lo que no peudes mezclar caballos con manzanas, como estás haciendo.
No es lo mismo hablar de Facturas que Pagos.

En todo caso, si en cada línea debe aparecer una factura con sus pagos, eso es una consulta agrupada con agregación y subtotales, y la sintaxis dependerá del DBMS usado... que no nos estás diciendo.

¿Qué DBMS se supone que usas?

[marenas4001]

Gracias por tu respuesta,
Estoy usando MySQL

[leonardo_josue]

Hola marenas4001:

No se si entendí correctamente el problema, pero creo que la confusión viene en entender como funciona la cláusula WHERE en el UNION.

Veamos este ejercicio para ver si es más o menos lo que quieres, supongamos que tienes estas dos tablas:

Código MySQL:

mysql> SELECT * FROM tabla1;
+------+-------------+
| id   | descripcion |
+------+-------------+
|    1 | uno         |
|    2 | dos         |
|    3 | tres        |
+------+-------------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM tabla2;
+------+-------------+
| id   | descripcion |
+------+-------------+
|    1 | one         |
|    2 | two         |
|    3 | three       |
+------+-------------+
3 rows in set (0.00 sec)
 

tal como lo comento gnzsoloyo, UNION nos sirve para encadenar resultados, es decir, si quieres obtener por ejemplo lo los elementos con ID = 1 de ambas tablas, podrías hacer esto:

Código MySQL:

mysql> SELECT id, descripcion FROM tabla1
    -> UNION
    -> SELECT id, descripcion FROM tabla2 WHERE id = 1;
+------+-------------+
| id   | descripcion |
+------+-------------+
|    1 | uno         |
|    2 | dos         |
|    3 | tres        |
|    1 | one         |
+------+-------------+
4 rows in set (0.00 sec)
 

¿qué paso entonces? que el filtro WHERE sólo afecta a la segunda parte de la consulta... en tu caso mencionas que quieres filtrar por el campo ENTITY, sin embargo este campo SÓLO EXISTE EN LA SEGUNDA PARTE DEL UNION

Aquí tendrías dos opciones, una sería agregar un FILTRO EN AMBAS CONSULTAS, es decir, algo como esto:

Código MySQL:

mysql> SELECT id, descripcion FROM tabla1 WHERE id = 1
    -> UNION
    -> SELECT id, descripcion FROM tabla2 WHERE id = 1;
+------+-------------+
| id   | descripcion |
+------+-------------+
|    1 | uno         |
|    1 | one         |
+------+-------------+
2 rows in set (0.00 sec)
 

o en su defecto, hacer primero la UNION y después filtrar el resultado con una subconsulta, es decir, algo como esto:

Código MySQL:

mysql> SELECT * FROM
    -> ( SELECT id, descripcion FROM tabla1
    ->   UNION
    ->   SELECT id, descripcion FROM tabla2
    -> ) T
    -> WHERE id = 1;
+------+-------------+
| id   | descripcion |
+------+-------------+
|    1 | uno         |
|    1 | one         |
+------+-------------+
2 rows in set (0.00 sec)
 

No sé si esto es lo que necesitas, haz la prueba y nos comentas.

Saludos
Leo.

[marenas4001]

Efectivamente, con agregar este WHERE a la primera consulta, funciona el filtro perfectamente.
Te agradezco tu respuesta.

Tambien tengo que decir que si estoy mezclando dos cosas distintas, el problema es que necesito las dos lineas separadas una por Facturas y otra por Pagos en la misma consulta(no hay problema si lo hago por codigo) desgraciadamente es para un reporteador (Reportico Ver 1.0) que no soporta muy bien el uso de UNION ni permite codigo PHP y si genero la consulta en una sola linea no tengo forma de separala para su presentacion.