SELECT d.id_order, os.name AS payment, d.product_name, d.product_reference, d.product_price, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(' ', g.firstname, g.lastname) AS Customer_name, g.id_customer AS CustomerID, CONCAT_WS(' ', ad.address1, ad.address2, ad.city, ad.postcode, ad.other, 'Mobile: ', ad.phone_mobile) AS Delivery_Address, CONCAT_WS(' ', ai.address1, ai.address2, ai.city, ai.postcode, ai.other, 'Mobile: ', ai.phone_mobile) AS Invoice_Address, gl.name AS group_name, s.quantity AS quantity_in_stock, g.email
FROM order_detail d
LEFT JOIN orders o ON (d.id_order = o.id_order)
LEFT JOIN address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN stock_available s ON (d.product_id = s.id_product)
LEFT JOIN customer g ON (o.id_customer = g.id_customer)
LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group) AND gl.name LIKE 'piiri%'
LEFT JOIN order_state_lang os ON (o.current_state = os.id_order_state)
WHERE os.id_lang = 1
The tables that are causing you issues are group_lang and stock_available.
Table group_lang has compound primary key (`id_group`,`id_lang`), but you join in only using gl.id_group. You need to include language condition as well:
LEFT JOIN group_lang gl ON (g.id_default_group = gl.id_group and gl.id_lang = 1)
Stock available is more complicated. Depending on you multistore setup, there can be a lot of entries for each product. First of all, for every combination there is a separate entry, and there is also an entry for combination with id = 0. If you use multistore, and each store has it's own quantity, then there are other records identified by id_shop or id_shop_group as well.
If you don't use multistore, then you should just add id_product_attribute into the join clause:
LEFT JOIN stock_available s ON (d.product_id = s.id_product AND s.id_product_attribute = d.product_attribute_id)
Alternatively, you can get rid of this table from the 'FROM', and use subquery. Instead of s.quantity AS quantity_in_stock, you would put this expression instead (and remove LEFT JOIN stock_available s ON from FROM clause):
// this to calculate quantity for product
(SELECT sum(s.quantity) FROM stock_available s WHERE d.product_id = s.id_product) as quantity_in_stock
// or this to calculate quantity available per combination
(SELECT sum(s.quantity) FROM stock_available s WHERE d.product_id = s.id_product AND s.id_product_attribute = d.product_attribute_id) as quantity_in_stock