Export Invoice Data SQL help!

[madpugger]

I am trying to export the following data and I am struggling with the SQL query required. Can someone help?!

                              

I have the first 6 rows, but then I am stuck on how to extract the rest!

 

SELECT pr_order_invoice.total_paid_tax_excl, pr_order_invoice.total_paid_tax_incl, pr_order_invoice.date_add, pr_orders.invoice_number, pr_orders.id_order, pr_orders.payment FROM pr_order_invoice, pr_orders WHERE pr_order_invoice.number = pr_orders.invoice_number;

 

TIA

[lordignus]

SELECT
    oi.total_paid_tax_excl AS 'Total EX',
    oi.total_paid_tax_incl AS 'Total Inc',
    oi.date_add AS 'Inv Date',
    o.invoice_number AS 'Inv No',
    o.id_order AS 'Order ID',
    o.payment AS 'Payment Type',
    osl.name AS 'Order Status',
    c.company AS 'Company',
    c.firstname AS 'Customer First',
    c.lastname AS 'Customer Last',
    cl.name AS 'Country'
FROM pr_order_invoice oi
LEFT JOIN pr_orders o
ON oi.id_order = o.id_order
LEFT JOIN pr_order_state_lang osl
ON o.current_state = osl.id_order_state AND osl.id_lang = 1
LEFT JOIN pr_customer c
ON c.id_customer = o.id_customer
LEFT JOIN pr_address a
ON o.id_address_delivery = a.id_address
LEFT JOIN pr_country_lang cl
ON a.id_country = cl.id_country AND cl.id_lang = 1
ORDER BY oi.id_order DESC

 

Depending on your language you might need to change the id_lang on lines 17 and 24

Edited December 8, 2021 by lordignus (see edit history)

[Knowband Plugins]

Please check the Below SQL query:

SELECT poi.total_paid_tax_excl AS "Total EX",poi.total_paid_tax_incl AS "Total Inc",poi.date_add AS "Inv Date",o.invoice_number AS "Inv No",o.id_order AS "Order ID",o.payment AS "Payment Type",posl.name AS "Order Status",c.company AS "Company",c.firstname AS "Customer First",c.lastname AS "Customer Last",cl.name AS "Country" FROM pr_order_invoice poi LEFT JOIN pr_orders o ON poi.id_order = o.id_order LEFT JOIN pr_order_state_lang posl ON o.current_state = posl.id_order_state LEFT JOIN pr_customer c ON c.id_customer = o.id_customer LEFT JOIN pr_address a ON o.id_address_delivery = a.id_address LEFT JOIN pr_country_lang cl ON a.id_country = cl.id_country ORDER BY poi.id_order DESC

[madpugger]

Hi, this worked great, thanks so much!