SQL query to get products in multiple order states

We have a products, orders, order_items, order_status schema as shown below. The orders tables status field, takes the values; ORDERED, RECEIVING, RECEIVED and CANCELED, as defined in the order_status table.

The UI, that interacts with this data do have a 'product view', with rows for each product. As a user select a product, another view below (In Orders), lists the orders that the selected product was present in.

The user should be able to filter the products view, based on product order status. In particular, a button, saying 'On Order', should filter the product view to only show product records that have a record in the In Orders view, with a status of 'ORDERED'.

The following query returns multiple product rows, originating from the fact that one product exists in multiple orders.

SELECT products.*,        
orders.`id` AS orderID,
orders.`status`    
FROM products 
LEFT JOIN order_items
 ON products.`id` = order_items.`product_id`
JOIN orders
 ON orders.`id` = order_items.`order_id`;

We want the above result set to "coalesce" on order status. That is, the result set should have only one product record for each distinct order status. We could then filter the product view on the 'status' field.

The image below shows what result set that we would like, based on the above result. Red means it should not be part of the result.

As observed from the image above;

• The product with ID 18 is repeated 3 times with the same status. We only want one of those rows.
• The product with ID 19 is present in 3 rows, two with the same status. Keep one of the two rows with the same status, and the row with status = 1.
• The product with ID 20 is repeated twice with the same status, keep one.

How to achieve that?