How to display order details using built-in SQL Manager

[Call_me_Dave]

Hi, could You please tell me please what is wrong with this SQL query? Prestashop returns no result at all .

SELECT ps_order_invoice.id_order_invoice, 
ps_orders.id_order, 
ps_order_detail.total_price_tax_incl, 
ps_order_state.id_order_state, 
ps_orders.invoice_date, 
ps_orders.current_state`
FROM ps_orders
JOIN ps_order_detail USING id_orders
JOIN ps_order_invoice USING id_orders
JOIN ps_order_state USING id_orders

I want the final table to have these collumns: 

id_order_invoice

id_order

total_price_tax_incl

id_order_state

invoice_date

current_state

 

Thank YOu

[JeredBolton]

On line 3 you're using:

ps_order_detail.total_price_tax_incl

This will provide prices for each item in the order, rather than the overall order cost, so you probably want to use:

ps_orders.total_paid_tax_incl,

instead to give the overall order value, which means you don't need to JOIN with the ps_order_detail table.

 

At the end of line 6, you have a stray ` character which you need to remove.

 

For USING to wrok on lines 8 - 9 you need to provide the name of the common table column you wish to join on, in this case id_order. You also need to put the name of the column in brackets:

JOIN ps_order_invoice USING (id_order)

Line 10 will never work because the ps_order_state table does not contain an id_order column. Instead you need to supply a language ID and use the ps_order_state_lang table to look up what the current state of the order is using the current_state column from the orders table.

 

Something like this should do the trick:

SELECT ps_order_invoice.id_order_invoice,                                     
ps_orders.id_order,                                                           
ps_orders.total_paid_tax_incl,                                         
ps_order_state_lang.name AS current_state,                                                     
ps_orders.current_state AS id_order_state,
ps_orders.invoice_date                                                                                                       
FROM ps_orders                                                                          
JOIN ps_order_invoice USING (id_order)
JOIN ps_order_state_lang ON ps_order_state_lang.id_order_state=ps_orders.current_state WHERE ps_order_state_lang.id_lang=1