Consulta cruzada de 4 tablas

[lynxstyle]

Hola, estoy intentando hacer una extracción de un listado de clientes en prestashop para mostrarlo en pdf, este listado muestra los datos del cliente y las compras que ya a realizado, la primera tengo que sacar los datos de la tabla ps_customer y de la tabla ps_address y lo hago con INNER JOIN.

Código MySQL:

SELECT ps_customer.id_customer, ps_customer.firstname, 
ps_customer.lastname, address1, address2, city, postcode, phone, phone_mobile 
FROM ps_customer INNER JOIN ps_address ON s_address.id_customer=ps_customer.id_customer 
ORDER BY postcode;
 

La segunda parte es en la que me pierdo, por que para mostrar el producto que ha comprado primero tengo que cruzar con la tabla ps_orders que es donde se une el pedido con el cliente y consultar el nombre del pedido en la tabla ps_order_detail.

Gracias de antemano.

[leonardo_josue]

Hola lynxstyle:

¿Cuál es el problema? ¿por qué dices que te pierdes en la consulta? También tendrías que comentarnos si lo que pretendes es obtener en una sola consulta toda la información que vas a mostrar... no sé con qué herramienta vas a generar el PDF, pero es posible que puedas utilizar varias consultas para generar toda la información que necesitas.

Si la idea es obtener toda la información con una sola consulta, es posible que tengas información duplicada, imagina el siguiente ejemplo:

Código MySQL:

mysql> SELECT * FROM clientes;
+-----------+---------------+
| idCliente | nombreCliente |
+-----------+---------------+
|         1 | uno           |
|         2 | dos           |
|         3 | tres          |
+-----------+---------------+
3 rows in set (0.32 sec)
 
mysql> SELECT * FROM pedidos;
+----------+-----------+-------------+
| idPedido | idCliente | fechaCompra |
+----------+-----------+-------------+
|        1 |         1 | 2013-04-14  |
|        2 |         2 | 2013-05-30  |
|        3 |         2 | 2013-06-05  |
|        4 |         1 | 2013-06-10  |
|        5 |         1 | 2013-06-04  |
+----------+-----------+-------------+
5 rows in set (0.00 sec)
 
mysql> SELECT * FROM detalle_pedidos;
+-----------------+----------+-------------------+----------+
| idDetallePedido | idPEdido | producto          | cantidad |
+-----------------+----------+-------------------+----------+
|               1 |        1 | producto uno.1    |        1 |
|               2 |        1 | producto uno.2    |        2 |
|               3 |        2 | producto dos.1    |        3 |
|               4 |        3 | producto tres.1   |        6 |
|               5 |        4 | producto cuatro.1 |        7 |
|               6 |        5 | producto cinco.1  |        2 |
|               7 |        5 | producto cinco.2  |        1 |
+-----------------+----------+-------------------+----------+
7 rows in set (0.00 sec)
 
mysql> SELECT c.*, p.fechaCompra, dp.producto, dp.cantidad
    -> FROM clientes c
    -> INNER JOIN pedidos p ON c.idCliente = p.idCliente
    -> INNER JOIN detalle_pedidos dp ON dp.idPedido = p.idPedido
    -> ORDER BY c.idCliente, p.fechaCompra, dp.producto;
+-----------+---------------+-------------+-------------------+----------+
| idCliente | nombreCliente | fechaCompra | producto          | cantidad |
+-----------+---------------+-------------+-------------------+----------+
|         1 | uno           | 2013-04-14  | producto uno.1    |        1 |
|         1 | uno           | 2013-04-14  | producto uno.2    |        2 |
|         1 | uno           | 2013-06-04  | producto cinco.1  |        2 |
|         1 | uno           | 2013-06-04  | producto cinco.2  |        1 |
|         1 | uno           | 2013-06-10  | producto cuatro.1 |        7 |
|         2 | dos           | 2013-05-30  | producto dos.1    |        3 |
|         2 | dos           | 2013-06-05  | producto tres.1   |        6 |
+-----------+---------------+-------------+-------------------+----------+
7 rows in set (0.00 sec)
 

Observa que el dato del cliente se repite n veces... esto en realidad para un reporte está de más, pero bueno, esa es cuestión de la lógica que quieras llevar en tu reporte.

Desde mi punto de vista no hay nada extraordinario en la consulta que quieres hacer, simplemente tienes que hacer todos los JOIN's sobre las tablas... No posteas la estructura de tus tablas, ni nos pones tampoco datos de ejemplo por lo que resulta complicado darte una respuesta más puntual.

La idea sería hacer algo como esto:

Código MySQL:

...
FROM ps_customer 
INNER JOIN ps_address 
  ON ps_address.id_customer=ps_customer.id_customer 
INNER JOIN ps_orders  
  ON Aquí pones la relación entre las tablas ps_customer y ps_orders
INNER JOIN ps_order_detail
  ON Aquí pones la relación entre las tablas os_orders y ps_order_detail
...
 

Haz la prueba y nos comentas. Si continuas con problemas postea la estructura de tus tablas, pon algunos datos de ejemplo y dinos más cláramente qué es lo que quieres obtener como resultado.

Saludos
Leo.

[lynxstyle]

Ok a ver si me explico mejor, que el lio que tengo en la cabeza lo he plasmado antes, no pongo la estructura de las tablas completas por que las tablas en prestashop tienen muchos campos, muestro los que interesan.



efectivamente mi intención es hacerlo en una sola consulta y los datos que quiero mostrar, son el nombre y apellidos, dirección completa, teléfono, móvil y productos comprados, de ahí que tenga que consultar a las 4 tablas a la vez.

Código MySQL:

SELECT ps_customer.id_customer, ps_customer.firstname,
ps_customer.lastname, address1, address2, city, postcode, phone, phone_mobile
FROM ps_customer INNER JOIN ps_address ON s_address.id_customer=ps_customer.id_customer
ORDER BY postcode;
 

de momento con esto extraigo todos los datos excepto los productos comprados.

[leonardo_josue]

Hola de nuevo:

Intentaste agregar todos los JOIN's como te puse en el ejemplo???

Saludos
Leo.

[lynxstyle]

Si pero no funciona, quedó así.

Código MySQL:

SELECT ps_customer.id_customer, ps_customer.firstname, ps_customer.lastname,
address1, address2, city, postcode, phone, phone_mobile, product_name
FROM ps_customer INNER JOIN ps_address ON ps_address.id_customer=ps_customer.id_customer
INNER JOIN ps_orders ON ps_orders.id_customer=ps_customer.id_customer
INNER JOIN ps_orden_detail ON ps_order_detail.id_order=ps_orders.id_order ORDER BY postcode
 

No entiendo el INNER con ps_orden_detail por que no tiene relacion con la tabla ps_customer como las demás.

[gnzsoloyo]

Tu consulta está correctamente escrita, y representa perfectamente la relación descripta en el diagrama.
Limpiándola un poco queda:

Código MySQL:

SELECT 
    C.`id_customer`,
    C.`firstname`,
    C.`lastname`,
    `address1`,
    `address2`,
    `city`,
    `postcode`,
    `phone`,
    `phone_mobile`,
    `product_name`
FROM
    `ps_customer` C
        INNER JOIN
    `ps_address` A ON C.`id_customer` = A.`id_customer`
        INNER JOIN
    `ps_orders` O ON C.`id_customer` = C.`id_customer`
        INNER JOIN
    `ps_orden_detail` OD ON O.`id_order` = OD.`id_order`
ORDER BY `postcode`
 

Lo que no ves, porque no conoces los fundamentos del modelo relacional, es que sí existe una relación entre el ps_customer y ps_order_detail, porque esa relación es transitiva, es decir que la relación surge de la existencia de un registro en ps_order que está relacionado con el ps_customer por su id_customer.
¿Por qué dices que "no funciona"?
¿En qué sentido no funciona?

[lynxstyle]

Efectivamente esta correcta, tenia yo un error que puse ps_ordeN_detail en vez de ps_ordeR_detail, con el echo ya funciona ahora a investigar el html que creo con esta consulta que no me da resultados.

gracias a los dos.