CODEWITHSUNDEEP
mysqlsqljoinleft-joingreatest-n-per-group
Totte Karlsson
Totte Karlsson

Reputation: 1341

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.

Schema for products and orders.

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`;

Result from above query

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.

Desired result

As observed from the image above;

How to achieve that?

Upvotes: 0

Views: 3606

Answers (2)

Barmar
Barmar

Reputation: 781868

Use GROUP BY to collapse multiple rows into one. Use MIN(o.id) to get a well-defined order ID within each group.

SELECT p.*, MIN(o.id) AS orderID, o.status
FROM products AS p
JOIN order_items AS oi ON oi.product_id = p.id
JOIN orders AS o ON o.id = oi.order_id
GROUP BY p.id, o.status

It doesn't make sense to use LEFT JOIN in this case. You never want to group by a column that comes from a LEFT JOIN table, since all the rows with no match will be grouped together. And if you're filtering on order status, you obviously only want products that are in an order.

Upvotes: 1

GMB
GMB

Reputation: 222582

If you are running MySQL 8.0, you can use row_number() for filtering. I guess the logic you want is:

select *
from (
    select 
        p.*,        
        o.id as orderid,
        o.status    ,
        row_number() over(partition by p.id, o.status order by o.id) rn
    from products p
    inner join order_items oi on p.id = oi.product_id
    inner join orders o on o.id = oi.order_id
) t
where rn = 1

I don't think that mixing inner join and left join make sense here. Either use two inner joins (as in the above query), or two left joins if you want to retain products without any order.

Upvotes: 1

Related Questions