Ayuda con SQL y 3 Tablas

hmoner · #1 03-05-2005

Holas amigos...
Tengo una duda existencial, pero facil para uds.
tengo 3 entidades:

PROMOCION (id,nombre,dinero,puntos)
CLIENTE (id,nombre,telefono)
COMPRA (id,idcliente,idpromo,fecha,monto)

Un comercio tiene varias promociones para sus clientes. En cada promocion ofrece X puntos por cada XX $ que el cliente compra.

El listado de cada tabla seria mas o menos

PROMOCION
1 PROMO1 100 10
2 PROMO2 50 1

CLIENTE
1 PEPE 232323
2 RAUL 222737
3 IGNACIO 3894394

COMPRA
1 2 1 25/3 $145,80
2 3 1 25/3 $145,80
3 2 2 26/3 $145,80
4 2 1 28/3 $145,80
5 1 2 29/3 $145,80

**************

Mi problema ahora es:
que consulta SQL puedo hacer para:

- Listar de esta forma ?

RESUMEN
NOMBRE | MONTOCOMPRADO | PUNTOS
PEPE XXX $ QQQ
RAUL XXX $ QQQ
IGNACIO XXX $ QQQ

Es decir, necesito hacer todo esto en un sql:

- Listar todos los clientes, cada uno con el monto total de compras que hizo

- A su vez, calcular cuantos puntos le corresponden en total, por cada promocion en la que participo (compro)...

Alguien tiene idea de como hacerlo ?
No se por donde empezar

Gracias

fly · #2 03-05-2005

Así, al vuelo, creo que quedaría :

Código SQL [-]SELECT CL.nombre AS Cliente, SUM(C.monto) AS Total, SUM(C.monto / P.dinero * P.puntos) AS Puntos
FROM Compra C
INNER JOIN Cliente CL ON CL.id=C.idcliente
LEFT JOIN Promocion P ON P.id=C.idpromo
GROUP BY CL.nombre
ORDER BY CL.nombre

Pongo LEFT JOIN Promocion por si existe alguna venta que no tiene promoción. Así el TOTAL los es de todas las compras, tengan o no promoción.

Si necesitaras que el listado salga separando promociones :

Código SQL [-]SELECT CL.nombre AS Cliente, SUM(C.monto) AS Total, SUM(C.monto / P.dinero * P.puntos) AS Puntos, P.nombre AS Promo
FROM Compra C
INNER JOIN Cliente CL ON CL.id=C.idcliente
INNER JOIN Promocion P ON P.id=C.idpromo
GROUP BY CL.nombre, P.nombre
ORDER BY CL.nombre

Por tanto TOTAL lo será de cada promoción, excluyendo las compras hechas sin promoción.

Bueno, sin poder compilarlo es todo lo que puedo hacer

no te aseguro que funcione, pero los tiros van por ahí.

Saludos y suerte.

hmoner · #3 03-05-2005

Hola fly
Mil gracias por la ayuda...

La 1º consulta funciono de maravillas !!! eres un maestro !
pero la 2º consulta me ha dado el siguiente error:

SELECT CL.nombre AS Cliente, SUM(C.monto) AS Total, SUM(C.monto / P.dinero * P.puntos) AS Puntos, P.nombre AS Promo
FROM Compra C
INNER JOIN Cliente CL ON CL.id=C.idcliente
INNER JOIN Promocion P ON P.id=C.idpromo
GROUP BY CL.nombre, P.nombre
ORDER BY CL.nombre

Error: P.nombre AS Promo must appear before aggregate columns in the selection list [no error code]

y luego la puse asi, tal cual dice el error, y funciono...

SELECT P.nombre AS Promo, CL.nombre AS Cliente, SUM(C.monto) AS Total, SUM(C.monto / P.euros * P.puntos) AS Puntos
FROM FACTURACION C
INNER JOIN CLIENTE CL ON CL.id=C.idcliente
INNER JOIN PROMOCION P ON P.id=C.idpromo
GROUP BY CL.nombre, P.nombre
ORDER BY CL.nombre

DE MARAVILLAS !!

Millon de gracias man !!

hmoner · #4 03-05-2005

por cierto fly
tu usas algun constructor de consultas ? osea, alguna herramienta que te ayude ? o sabes de algo que me pueda servir para este proposito ? y que soporte consultas "complejas" de este tipo con varios join, etc. ?
Solo necesito una herramienta q me ayude a construir estas sentencias SQL para no estar preguntando y preguntando y molestando

fly · #5 03-05-2005

Nada tranquilo, no necesitas ninguna herramienta en absoluto.
Estas sentencias son "sencillas" en el fondo, y además creo que es mejor crearlas uno mismo, es cuestión de practica; al principio puede que salgan largas y poco optimizadas, pero con el tiempo se aprende a sacarle todo el partido (o por lo menos a que hagan el trabajo).

Yo estoy aprendiendo a los pocos, empecé con un tuto que creo que es para tablas access y ahora a los pocos voy incorporando las novedades del fb 1.5

Si quieres algún tuto te lo puedo pasar, pero en internet hay de todos los gustos y colores.

Saludos y encantado de que te sirviera.

PD: No creo que molestes con tus preguntas, para eso están los foros.

hmoner · #6 03-05-2005

si tienes algun tutorial bueno bueno, que me sirva para aprender un 50% de lo q sabes tu, con eso me conformo

gracias master !

fly · #7 03-05-2005

je, je, je...

Ahora mismo en favoritos no tengo nada...
Pero con algo de busqueda encontre el primero que usé y parece que es bastante conocido (disculpe el autor mi ignorancia) lo puedes descargar de aquí http://www.abcdatos.com/tutoriales/tutorial/l965.html en el está el 180% del SQL que se, no es para FB/Interbase pero va bien.
Es de Claudio Casares y desde ese link podrá acceder a más tutos.

Mira esto http://www.aoindustries.com/docs/int...ef/SqlRef.html
Es muy completito (ya ya ni recordaba que lo tenia)

No tengo más, la verdad es que cada vez que necesito algo lo busco y lo descargo, o cut-paste y pa´casa. Así que links no tengo muchos.

Por último mira en la carpeta doc\sql.extensions de firebird, y verás las novedades del motor.

Saludos y ánimo, no tengas prisa que esto hay que aprenderlo a los pocos.

hmoner · #8 03-05-2005

hola Amigazo

Gracias por las direcciones...

1º) En la consulta, cuando el sistema hace esto:
SUM(C.monto / P.dinero * P.puntos) AS Puntos
me da como resultado que todos los valores de PUNTOS tienen el simbolo $, como si fuera dinero, pero son puntos. Lo que hice fue cambiar el tipo de C.MONTO de currency a double y se soluciono...
pero ahora:
Como puedo hacer para que el valor PUNTOS me lo muestre solo con 2 decimales (en la misma consulta SQL) ... Se puede ?

2º) Por ahora, solo por ahora, no aprendi a usar ni Interbase, ni Firebird .. los he bajado y todo, etc. pero aqui en el trabajo han comprado licencia de www.nexusdb.com por lo que ahora estoy usando este para todo..

Gracias de nuevo amigo =)

fly · #9 04-05-2005

Ordia!! juraría que ya respondí esto ayer....(debo dormir más)

Bueno hmoner, gracias por tu confianza, pero deberias poner esa consulta en otro hilo ya que no viene al caso en este.

Yo lo de los decimales lo soluciono con una funcion UDF creada para tal propósito, pero dado que no usas interbase/firebird, no se si vale la solución.

Las funciones UDF (User Defined Function) son subrutinas ubicadas en una DLL que el motor de la BBDD puede usar igual que las internas. Yo las hago en delphi y despues sólo hay que incluirlas en la BBDD.

Si no encuentras otra solución investiga si existe algo similar en nexusdb.

Saludos.

hmoner · #10 05-05-2005

Hola fly !!
Gracias por la ayuda... sin ese empujoncito no se que hubiera hecho... la consulta funciona de maravillas...
Por lo de los decimales, gracias a Dios lo solucione con la TExRxDbGrid, que es una extension o modificacion de la TRxDbGrid... que forma parte del famoso pack de componentes RxLib que seguramente has probado...

Y para finalizar, me aprovecho de vuestra amabilidad y comienzo otro hilo

Saludos desde Argentina

fly · #11 06-05-2005

Cita:

Empezado por hmoner

que forma parte del famoso pack de componentes RxLib que seguramente has probado...

Pues no señor, yo uso IbQuerys, Edits y DBGrids; de lo más simplón que te puedas echar a la cara, todo lo demás lo hago a pedal.

Saludos

hmoner · #12 06-05-2005

aaaajajajajaj
ok...

aprovechando a que estas online fly...

viste el otro hilo que comence ? ...

me han enviado una consulta muy compleja y larga que no me funciona

yo tengo esto:

SELECT CLIENTE.NOMBRE AS CLIENTE, SUM(FACTURACION.MONTO) AS MONTO, SUM(FACTURACION.PUNTOS) AS PUNTOS
FROM CLIENTE LEFT JOIN FACTURACION ON FACTURACION.IDCLIENTE=CLIENTE.ID
GROUP BY CLIENTE.NOMBRE
ORDER BY CLIENTE.NOMBRE

que en realidad me muestra el total facturado a cada cliente...

como puedo separar, (con subquery?) y agregarle algo para que me muestre el total normal y el total facturado por promocion ?...

siendo FACTURACION.ESPECIAL un campo BOOLEAN donde guardo si la facturacion fue especial o no

Gracias...