SQL Query para extraer costo de envío

[Jonnathan]

Hola comunidad,

Vengo a solicitar un poco de ayuda en SQL ya que no es mi fuerte.

He modificado esta query para que me muestre cierta información:
ID, nombre del cliente, referencia, costo total, método de pago, estado, currier, fecha

Me trae la información que necesito peeero necesito agregar la data del costo de envío y no tengo idea de como hacerlo 🙄

 

SELECT
	o.id_order AS ID,
	CONCAT(LEFT(cu.`firstname`, 1), '. ', cu.`lastname`) AS `CLIENTE`,
	o.reference AS `COD DE PEDIDO`,
	o.total_paid_tax_incl AS `TOTAL`,
	o.payment AS `METODO DE PAGO`,
	osl.name AS `ESTADO`,
	ca.`name` AS `CURRIER`,
	o.date_add AS `FECHA`,
IF ((SELECT so.id_order FROM ldm_orders so WHERE (so.id_customer = o.id_customer) AND (so.id_order < o.id_order) LIMIT 1) > 0, 0, 1) AS new
FROM ldm_orders o
	LEFT JOIN ldm_customer cu ON o.id_customer = cu.id_customer
	LEFT JOIN ldm_carrier ca ON o.id_carrier = ca.id_carrier
	LEFT JOIN ldm_currency cur ON o.id_currency = cur.id_currency
	INNER JOIN ldm_address a ON o.id_address_delivery = a.id_address
	LEFT JOIN ldm_order_state os ON o.current_state = os.id_order_state
	LEFT JOIN ldm_shop s ON o.id_shop = s.id_shop
	INNER JOIN ldm_country c ON a.id_country = c.id_country
	INNER JOIN ldm_country_lang cl ON c.id_country = cl.id_country AND cl.id_lang = 1
	LEFT JOIN ldm_order_state_lang osl ON os.id_order_state = osl.id_order_state AND osl.id_lang = 1
	WHERE (o.`id_shop` IN ('1'))
	AND (o.`date_add` >= '2021-03-01 0:0:0')
	AND (o.`date_add` <= '2021-03-27 23:59:59')
	ORDER BY o.`date_add` desc LIMIT 999

 

La tabla que almacena la data es esta según lo que investigue

Si alguien me pudiera ayudar un poco con la sentencia sería estupendo.

Saludos.

PS: v1.7.7.1

BD: mariadb 10.5.8

Edited March 27, 2021 by Jonnathan (see edit history)

[Franciscobp92]

Si aún no lo has resuelto, eso le encuentras en la tabla ldm_orders, en los campos total_shipping , total_shipping_tax_excl, total_shipping_tax_incl. Depende de lo que necesites sacar

[Jonnathan]

23 hours ago, Franciscobp92 said:

Si aún no lo has resuelto, eso le encuentras en la tabla ldm_orders, en los campos total_shipping , total_shipping_tax_excl, total_shipping_tax_incl. Depende de lo que necesites sacar

Aún no lo tengo resuelto, me pudieras ayudar facilitandome la query, no tengo skills en sql 😑

[Franciscobp92]

1 hour ago, Jonnathan said:

Aún no lo tengo resuelto, me pudieras ayudar facilitandome la query, no tengo skills en sql 😑

De esta forma, lineas 7 y 8, si necesitas ambas las dejas, caso contrario borras la que no te sirva.

 

SELECT
	o.id_order AS ID,
	CONCAT(LEFT(cu.`firstname`, 1), '. ', cu.`lastname`) AS `CLIENTE`,
	o.reference AS `COD DE PEDIDO`,
	o.total_paid_tax_incl AS `TOTAL`,
	o.payment AS `METODO DE PAGO`,
	o.total_shipping as `TOTAL DE ENVIO`,
	o.total_shipping_tax_excl as `TOTAL DE ENVIO SIN IMPUESTOS`,
	osl.name AS `ESTADO`,
	ca.`name` AS `CURRIER`,
	o.date_add AS `FECHA`,
IF ((SELECT so.id_order FROM ldm_orders so WHERE (so.id_customer = o.id_customer) AND (so.id_order < o.id_order) LIMIT 1) > 0, 0, 1) AS new
FROM ldm_orders o
	LEFT JOIN ldm_customer cu ON o.id_customer = cu.id_customer
	LEFT JOIN ldm_carrier ca ON o.id_carrier = ca.id_carrier
	LEFT JOIN ldm_currency cur ON o.id_currency = cur.id_currency
	INNER JOIN ldm_address a ON o.id_address_delivery = a.id_address
	LEFT JOIN ldm_order_state os ON o.current_state = os.id_order_state
	LEFT JOIN ldm_shop s ON o.id_shop = s.id_shop
	INNER JOIN ldm_country c ON a.id_country = c.id_country
	INNER JOIN ldm_country_lang cl ON c.id_country = cl.id_country AND cl.id_lang = 1
	LEFT JOIN ldm_order_state_lang osl ON os.id_order_state = osl.id_order_state AND osl.id_lang = 1
	WHERE (o.`id_shop` IN ('1'))
	AND (o.`date_add` >= '2021-03-01 0:0:0')
	AND (o.`date_add` <= '2021-03-27 23:59:59')
	ORDER BY o.`date_add` desc LIMIT 999

 

[Jonnathan]

Muchas gracias @Franciscobp92 por tomarte el tiempo de ayudar a otros, funciono perfectamente espero le puede servir a más personas

La query queda de la siguiente manera:
 

SELECT
	o.id_order AS ID,
	CONCAT(LEFT(cu.`firstname`, 1), '. ', cu.`lastname`) AS `CLIENTE`,
	o.reference AS `COD DE PEDIDO`,
	o.total_paid_tax_incl AS `TOTAL`,
	o.payment AS `METODO DE PAGO`,
	osl.name AS `ESTADO`,
	ca.`name` AS `CURRIER`,
        o.total_shipping as `TOTAL DE ENVIO`,
	o.date_add AS `FECHA`,
IF ((SELECT so.id_order FROM ldm_orders so WHERE (so.id_customer = o.id_customer) AND (so.id_order < o.id_order) LIMIT 1) > 0, 0, 1) AS new
FROM ldm_orders o
	LEFT JOIN ldm_customer cu ON o.id_customer = cu.id_customer
	LEFT JOIN ldm_carrier ca ON o.id_carrier = ca.id_carrier
	LEFT JOIN ldm_currency cur ON o.id_currency = cur.id_currency
	INNER JOIN ldm_address a ON o.id_address_delivery = a.id_address
	LEFT JOIN ldm_order_state os ON o.current_state = os.id_order_state
	LEFT JOIN ldm_shop s ON o.id_shop = s.id_shop
	INNER JOIN ldm_country c ON a.id_country = c.id_country
	INNER JOIN ldm_country_lang cl ON c.id_country = cl.id_country AND cl.id_lang = 1
	LEFT JOIN ldm_order_state_lang osl ON os.id_order_state = osl.id_order_state AND osl.id_lang = 1
	WHERE (o.`id_shop` IN ('1'))
	AND (o.`date_add` >= '2021-04-01 0:0:0')
	AND (o.`date_add` <= '2021-04-31 23:59:59')
	ORDER BY o.`date_add` desc LIMIT 999

Y te arroja la info de esta forma

[Sebastian_110]

Hola @Franciscobp92 , ¿cómo se le puede agregar la descripción del producto a esta query? Para saber que compró. Soy nivel básico en SQL. 

Gracias!

[Franciscobp92]

2 hours ago, Sebastian_110 said:

Hola @Franciscobp92 , ¿cómo se le puede agregar la descripción del producto a esta query? Para saber que compró. Soy nivel básico en SQL. 

Gracias!

Que tal @Sebastian_110, la consulta SQL del post saca la información solo de cabeceras, para el detalle de las órdenes necesitas las tablas ps_order_detail y ps_product_lang.