SQL Finding Orders belonging to a product category

Question

Im trying to find all orders in the database where the product belongs to a product category. But the category is stored in the Orderline table.

My table structure is roughly:

Order Table

ID
Date

OrderLine Table

ID
Product_ID
ProductCategory_ID
Quantity

Product Category Table

ID
Name

My sql looks like:

Select 
 o.Id,
    o.Date,
    pf.Name,
From Order o
 JOIN OrderLine ol on o.Id = ol.Order_Id
 JOIN ProductCategory pc on ol.ProductCategory_Id = pc.Id

WHERE 
 pc.ID in ('1000','1001', '1002')

But I suffer from multiple lines per order when an order has multiple orderlines belonging to the same category. As I only want a summary, I just want one row per order.

Answer 1

Use the DISTINCT clause:

Select DISTINCT
    o.Id,
    o.Date,
    pc.Name,
From Order o
 JOIN OrderLine ol on o.Id = ol.Order_Id
 JOIN ProductCategory pc on ol.ProductCategory_Id = pc.Id
WHERE 
 pc.ID in ('1000','1001', '1002')

This will ensure that only distinct rows (rows that are different) are returned.

You also seem to have a syntax error in your SELECT clause (did you mean pc.Name instead of pf.Name?).

Answer 2

Use the GROUP BY statement to group multiple rows together using the specified columns.

Select 
 o.Id,
    o.Date,
    pc.Name,
From Order o
 JOIN OrderLine ol on o.Id = ol.Order_Id
 JOIN ProductCategory pc on ol.ProductCategory_Id = pc.Id
WHERE 
 pc.ID in ('1000','1001', '1002')
GROUP BY o.id, o.date, pc.Name