SQL customer orders + customer name

[martinBrazing]

Dear Community,

I am trying to build simple report of customer orders and failing to join customer name based on id_customer key...can You please advise?

Thanks for help!

SELECT c.name, o.reference, o.date_add, s.name, d.product_name, d.product_quantity, d.product_quantity_in_stock
FROM pstm_order_detail d 
JOIN pstm_orders o ON (d.id_order = o.id_order) 
JOIN pstm_order_state_lang s ON (o.current_state = s.id_order_state) WHERE s.id_lang = 1 
JOIN pstm_customer c ON (o.id_customer = c.id_customer)

[hajeld]

Its creating a similar outcome likewise with mine. I am utilizing Clients and Requests Table on W3School. In the wake of utilizing this question, just one line is chosen.

[musicmaster]

I am just puzzled about Join following the where. Try something like:

SELECT c.name, o.reference, o.date_add, s.name, d.product_name, d.product_quantity, d.product_quantity_in_stock
FROM pstm_order_detail d 
JOIN pstm_orders o ON (d.id_order = o.id_order) 
JOIN pstm_order_state_lang s ON (o.current_state = s.id_order_state) 
JOIN pstm_customer c ON (o.id_customer = c.id_customer)
WHERE s.id_lang = 1 AND id_customer=100

 

[martinBrazing]

Thanks for replies ..... i have created new querry which seems to be working 

SELECT 
co.reference as 'CUSTOMER ORD', 
c.company as 'COMPANY',
co.date_add as 'CUSTOMER ORD DATE', 
sn.name as 'CUS ORD STATUS',
od.product_name as 'PRODUCT',
od.product_quantity as 'QUANTITY',
od.product_quantity_in_stock as 'QUANTITY in STOCK' ,
po.reference as 'PO REF',
po.date_add as 'PO DATE',
pos.name as 'PO STATUS',
s.name as 'SUPPLIER',
po.eta as 'PO ETA' 
FROM  pstm_bms_procurement_purchase_order po
JOIN pstm_supplier s ON po.supplier_id = s.id_supplier
JOIN  pstm_bms_procurement_purchase_order_status pos ON po.status_id = pos.id_bms_procurement_purchase_order_status
RIGHT JOIN pstm_orders co ON (CONVERT(po.comments_public, char(9)) = co.reference)
JOIN pstm_order_detail od ON (co.id_order = od.id_order)
JOIN pstm_customer c ON (co.id_customer = c.id_customer)
JOIN pstm_order_state_lang as sn ON (co.current_state = sn.id_order_state) WHERE sn.id_lang=1